select records where ID is not in a comma separated list in another table
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.
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
"SELECT memId, memCompany FROM clgolfsql.tblmembers ORDER BY memCompany"
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"
Hope that makes sense.
Many thanks in advance.