QUICKIE MYSQL question

ChicoMendez

Ive a quick mysql question for one of u gurus

I've three tables relating to members that would sign up to commissions:



Commissiontable
id
title
etc etc


Linktable
id
commissionid
memberid



Memebertable
id
name
etc etc

I know how to select all members that are on the commission:

SELECT m.id,m.name FROM Memebertable as m RIGHT JOIN Linktable as l ON (m.id=l.memberid) WHERE l.commissionid='the current commission'

However how do i select all members that ARE NOT on the commission???


Tnx

PhantomBeaker

Ok, I'm hoping you're using MySQL 4.1 because version 4.1 and up supports nested queries which is what you need.

I'd use something like (I've not tested this but this is the general gist)
[php]
SELECT m.id,m.name
FROM Memebertable as m
WHERE m.id <> all(SELECT m.id FROM Memebertable as m RIGHT JOIN Linktable as l ON (m.id=l.memberid) WHERE l.commissionid='the current commission')
[/php]

What that does is, it gets all the members from the table and excludes those who are on that commission using that query that you created to get all those members on commission)

Take care,
Aoife