SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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"

    Hope that makes sense.

    Many thanks in advance.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Normalise your database and get rid of comma separated values in a column.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido,

    I'd rather not change it as it would mean re-writing quite a few pages so hopefully there is a way of doing it whilst keeping the comma separated values.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by scim1971 View Post
    ... hopefully there is a way of doing it whilst keeping the comma separated values.
    oh, there is -- but it's slow as cold glue

    if you're happy with an app that doesn't scale (i.e. the more rows you have, the slower your app gets), then just carry on

    otherwise, bite the bullet, normalize your data, and make the code changes that are needed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    fair enough - I consider myself told off
    I'll re-code.
    Thanks anyway

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i wouldn't use the term "told off"

    you were given accurate, helpful advice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Still stuck.

    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

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT 
        memId
      , memCompany 
    FROM clgolfsql.tblmembers
    LEFT OUTER JOIN clgolfsql.tbldistclub 
    ON memID = diclGolfId
    WHERE diclGolfId IS NULL
    ORDER BY memCompany

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    brilliant - you're a genius Guido
    it works a treat.

    Many, many thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •