Selecting all committees that member does NOT belong to

bunner_bob

I know this is a left join of some kind, but I can't quite wrap my head around it.

Tables:

table Committees
CommitteeID
Title

table Members
MemberID
FirstName
LastName

table CommitteesMembers
CommitteeMemberID
CommitteeID
MemberID

I want to find all the Committees that the current member is NOT part of. So I need to go thru each CommitteeID and see if there's no entry in the third (CommitteesMembers) table containing the CommitteeID and the current MemberID.

I'm joining Committees and CommitteeMembers, right?

SELECT Committees.Title FROM Committees LEFT JOIN CommitteesMembers ON Committees.CommitteeID=CommitteesMembers.CommitteeID WHERE...

I know it's NOT "WHERE CommitteesMembers.MemberID IS NULL", because these committees DO have members, just not our current member. I need to work the current MemberID in there somehow.

Can someone give me a nudge in the right direction?

jheitz

Try this:

SELECT Committees.Title
FROM Committees
WHERE NOT EXISTS (SELECT 1 FROM CommitteesMembers WHERE MemberID="XXXXX" AND Committees.CommitteeID=CommitteesMembers.CommitteeID)

Roger_Ramjet

OK, you are nearly there. You just need to restrict the committemembers to the member in question and then left join to that. So how would one do that? By also joining the committemembers table to the members table with an inner join and a where condition that restricts the members to just the member in question.

SELECT Committees.* FROM Members m INNER JOIN CommitteMembers cm ON m.MemberID=cm.MemberID 
LEFT JOIN Committees c ON cm.CommitteeID=c.CommitteeID
WHERE m.MemberID=$id AND cm.CommitteeID IS NULL

bunner_bob

jheitz - can't do subqueries just yet because I'm having a little trouble with my new MySQL 4.1 upgrade...

Roger, I get zero results from your option. When I remove the last WHERE condition I get just the committees that the current member belongs to, so it seems if I'm further limiting my results then it isn't going to somehow add more results (the committees the member doesn't belong to). Am I missing something?

Also, the aliasing you suggest just errors for me - maybe because PHP seems to think I'm running MySQL 3.23...

Roger_Ramjet

OK, I did rather dash that one off. I'd do it with subqueries normally but was trying to do it without one. When I try it out in Access, which is what I use 99% of the time, it does not work because of 'ambiguous outer joins'. Same problem with mysql and any db I guess.

Outside the box then. AND AS I SHOULD HAVE SEEN FROM THE START 😕

If you know the members's id then you don't need the members table at all

SELECT DISTINCT c.* FROM Committees AS C LEFT JOIN CommitteeMembers as cm ON c.CommitteeID=cm.CommitteeID
WHERE cm.MemberID!=$memid OR cm.MemberID IS NULL

The LEFT JOIN and IS NULL bit is to capture committees with no members yet. Works a treat in access and should work a treat in mysql.

I feel like a dummy because I KNOW that you cannot go from table 1 to table 3 when faced with a chasm trap like this.

bunner_bob

Dude, if you're a dummy, I'm Angelina freakin' Jolie...

We're getting close. Now, if I have a committee that ONLY contains our test member, it is correctly ignored, and if it doesn't contain our test member, it is correctly retrieved. But if a committee contains our test member AND another member, it is retrieved. This is because when it sees the other member, it says "here's a member that doesn't match our test member - therefore, we have a hit".

I wonder if I shouldn't just select all committees that DO contain our test member, then do a "NOT IN" (assuming that's legal). Of course that's two queries (or a sub, once I get MySQL fixed). And so inelegant...

Roger_Ramjet

Yes, and I should have realised that that would happen. So, to me, I am a dummy; I've been doing sql for 20 years and should know better.

Of course, a subquery is the way to go; but then we were trying to do this without a subquery. Personally, I'd be doing this with a named view that listed all the committees a member was in - and left joining that with is null. But then I'd be working in access/sqlserver/oracle or another db that allowed named views/pre-compiled queries as elements in another query: choose the right tool for the job.

Now, if you can use a version of mysql that supports subqueries then use one: if not then use 2 queries, the first to create a work table with the committee ids that the member is in, then the next to select all committee ids not in that list. Can't think of any way to do it with just 1 query - nut there are a few sql gurus around here that will probably prove me wrong.

bunner_bob

Well, good news. I discovered that apparently subqueries ARE working for me, and in fact jheitz' option above works perfectly. I never tried it because some other attempts at subqueries failed me. Clearly I must do some more studying.

For instance, the subquery
NOT EXISTS (SELECT 1 FROM CommitteesMembers WHERE MemberID="XXXXX" AND Committees.CommitteeID=CommitteesMembers.CommitteeID)

What does "SELECT 1" mean?

I need to find a good primer (i.e. written for dunces, NOT the MySQL manual) on subqueries...

jheitz

What about this? You will need a subquery, but I don't believe this can be done without one...

SELECT c.
FROM Committees AS c LEFT JOIN (SELECT FROM CommitteeMembers WHERE MemberID=$memid) AS cm ON c.CommitteeID=cm.CommitteeID
WHERE cm.MemberID IS NULL

bunner_bob

Hey waitaminit - jheitz, your first idea worked just fine.

I think maybe I understand this one better though....and it works great too. Wow - two things that work - that's two more than I usually end up with.

Thank you. One of these days I will learn to think in SQL (especially those null-hunts - reminds me of languages with the verb at the end).

jheitz

Glad it helped. I personally like the "NOT EXISTS" approach better as it is maybe a bit easier to understand. As per which one performs better I am not too sure... Let us know if you find out...