Re: Advanced SQL
- From: "mailinglists@xxxxxxxxxxxx" <mailinglists@xxxxxxxxxxxx>
- To: programmingblind@xxxxxxxxxxxxx
- Date: Fri, 26 Aug 2011 12:58:53 -0400
It took me a day but, I have figured out the syntax:
Select all Topics and show their related Actions. All topics are shown
regardless of whether they have an Action associated with them.
select T.name as Topic, A.name as Action
from topic T left outer join relationship R on T.id = R.topic_id
left outer join action A on R.action_id = A.id
where T.state_id = 1
order by W.department_id, W.id
Thx,
Jeff
Original Message:
-----------------
From: Dorene Cornwell dorenefc@xxxxxxxxx
Date: Thu, 25 Aug 2011 20:51:44 -0700
To: programmingblind@xxxxxxxxxxxxx
Subject: Re: Advanced SQL
Hi Jeff
It looks to me like the relationship table has already done whatever
you need to add information from the Action table. Have I read that
correctly?
In that case, all you need to do is left join the topic table on topic
id to add whatever you need from the topic table. Then the topics
where there is nothing in the relationship table that matches by topic
id will show up with the topic and the action id missing. That is what
you want.
The exact syntax depends on what flavor of SQL you are working with.
It looks to me like
Have I misunderstood what you need to do?
DoreneC
Seattle WA
On 8/25/11, Jeff Berwick <mailinglists@xxxxxxxxxxxx> wrote:
> Hi Listers,
>
> I need some sql assistance.
>
> I have three tables that are connected in a many to many relationship.
The
> three tables are:
>
> topic, relationship (the many to many table) and Action.
>
> I want to show all the relationships but, if there are no relationships I
> need to still show the Topics. Sample tables might look like:
>
> Topic
> ++++++
> id
> +++++
> 1
> 2
> 3
> 4
>
> Relationship
> +++++++
> topic_id | action_id
> +++++++
> 1 | 1
> 3 | 2
> 4 | 3
> 4 | 4
>
> Action
> ++++++
> id
> +++++
> 1
> 2
> 3
> 4
>
> I know I need to use a left outer join at some point but, can't get this
> figured out. Any assistance is welcome and appreciated.
>
> TIA,
> Jeff
>
> __________
> View the list's information and change your settings at
> //www.freelists.org/list/programmingblind
>
>
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
--------------------------------------------------------------------
mail2web.com - Microsoft® Exchange solutions from a leading provider -
http://link.mail2web.com/Business/Exchange
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
Other related posts:
