Multi Table Select Statement 1

[sila]

Hi
I hope someone can halp me, I have the following tables:

Members
member_id

CommitteeMembers
committee_member_id
committee_id
member_id

Committees
committee_id
committee_name

AdvisoryGroupMembers
advisory_member_id
advisory_group_id
member_id

AdvisoryGroups
advisory_group_id
advisory_group_name

I need to be able to select as one list the advisory groups and committees that a member id belongs to. Any help greatly appreciated.

Thanks

 

[dyarwood]

What have you tried? You will need to do two outer joins and two inner joins as far as i can tell.

 

[JamesLean]

If you just want a single list of advisory groups and committees for a particular member id try this:

SELECT c.committee_name
FROM committees c JOIN committeemembers cm ON c.committee_id = cm.committee_id
WHERE cm.member_id = 123
UNION ALL
SELECT ag.advisory_group_name
FROM advisorygroups ag JOIN advisorygroupmembers agm ON ag.advisory_group_id = agm.advisory_group_id
WHERE agm.member_id = 123

--James

 

[sila]

Thanks again James.

 

[sila]

James

One more question on this query I've just realised! Both the tables CommitteeMembers and AdvisoryGroupMembers also have a field called 'position' which links to a table called Position where the position_name can be selected for the committee/advisory group how do I go about adding this in also?

Thanks

 

[JamesLean]

SELECT c.committee_name, p.position_name
FROM committees c
  JOIN committeemembers cm ON c.committee_id = cm.committee_id
  JOIN position p ON cm.position_id = p.position_id
WHERE cm.member_id = 123
UNION ...

I'll let you figure out the rest... ;-)

--James

 

[sila]

Cheers James!