SQL Query 2

[DarkConsultant]

Hi All,

I have a table called usergroup which contains group and subgroup names such ..

UIN   Name       SubGroupOf
 1    Contacts   NULL
 2    Prospects  1
 3    Dead       1

A group is a subgroup if it has a SubGroupOf value (Groups are NULL).

Another table is called members ...

UIN   GroupUIN  MemberUIN
 1        2        24
 2        1        32

So if I am selecting members.UIN=1 I want to return the name of the group (Prospects) and also the name of the parent group (Contacts).

How do I combine this into a single query returning both pieces of data in a single row?

Many thanks

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[feherke]

Hi

[b]select[/b]
[teal]*[/teal]

[b]from[/b] members m
[b]inner[/b] [b]join[/b] usergroup u [b]on[/b] u[teal].[/teal]uin[teal]=[/teal]m[teal].[/teal]groupuin
[b]left[/b] [b]join[/b] usergroup u2 [b]on[/b] u2[teal].[/teal]uin[teal]=[/teal]u[teal].[/teal]subgroupof

[b]where[/b] m[teal].[/teal]uin[teal]=[/teal][purple]1[/purple]

Feherke.

http://feherke.github.com/

 

[DarkConsultant]

Hi feherke,

Thanks for the speedy response, may I ask a question?

You have suggested an INNER JOIN on usergroup u, can I ask why not a LEFT JOIN as in the second join?

I am not nitpicking I have gaps in my SQL knowledge that I need to fill. The query seems to work the same if I use either joins.

Thanks in anticipation,

David

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[r937]

the INNER join is because it is assumed that each member belongs to a group

the LEFT OUTER join is because a group might not have a parent group

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[feherke]

Hi

Just as Rudy explained.

One thing to add : [tt]inner join[/tt] is generally faster than [tt]outer join[/tt], so when the data relations allow it, I always prefer [tt]inner join[/tt].


Feherke.

http://feherke.github.com/

 

[DarkConsultant]

Hi r937 and feherke,

Many many thanks for your replies. I am an app coder and only touch sql about once a year and so cannot keep these things in my head. I would like to do more sql but I cannot choose which contracts I am awarded.

I am currently working on an online database with mysql as the db engine. Now you have answered my sql query I am about a week from finishing, thanks again.

To finish off, my groups or subgroups do not have to contain any members, groups do not have to contain a subgroup and all subgroups have a parent group so I have used left joins for both and it has not produced any erroneous data during 2 hours of testing.

Thanks again guys ...

David Wadsworth

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//