select records where ID is not in a comma separated list in another table

The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

select records where ID is not in a comma separated list in another table

Hi,

I have a database with a table of "Golf Clubs" (tblmembers) - the unique ID reference is "memId" and the company name is "memCompany".

I also have a table listing all the salespeople (tblusers) - the unique ID is usrId. Also in this table I have a field (a text field) containing a comma separated list of all the golf clubs each salesman is repsonsible for (the memId from above - i.e. 1,2,3,4 etc)

This works fine and I can convert the comma seperated list into a list of the actual Golf Clubs when viewing each salesmans record.

When I add a new salesman (or edit their record) I have a multi select list of all the golf clubs (which creates the comma separated list) and that list is simply a full list of all the Golf Clubs - i.e.

Code:

"SELECT memId, memCompany FROM clgolfsql.tblmembers ORDER BY memCompany"

What I'd really like though is for the list of Golf Clubs (in my multi select list - using the above SQL) to only show Golf Clubs that are not already in another salesmans list of Golf Clubs. The salesmans table is clgolfsql.tblusers and the list of Golf Clubs is a field called usrDistClubs

In plain-ish English I want my select statement to say:

"select all Golf Clubs from table tblmembers that are not already in the comma separated lists in the field usrDistClubs in table tblusers"

I've now created a new table "tbldistclub" which contains a column for the Golf club "diclGolfId" and one for the salesman "diclUsrId" - so there will be one row for each golf club/saleman relationship (and each salesman can be associated with many golf clubs).

So now I need my select statement to generate a list of Golf Clubs (memId) that are not already in the new tbldistclub database (in the field diclGolfId):

SELECT memId, memCompany FROM clgolfsql.tblmembers, clgolfsql.tbldistclub WHERE ???????????? ORDER BY memCompany