#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    5

    MySQL Query - Last Column of Select as a nested Select?


    Hi All,

    Is there any way I can get an initial SQL query showing members of a group to have a last column of it, to show which groups each person is a member of?

    Initial Query:

    Code:
    SELECT tblClient.*, tblLookUp_Category.* 
        from tblClient 
        inner join tblClient_Category on tblClient.Client_ID = tblClient_Category.Client_ID 
        inner join tblLookUp_Category on tblClient_Category.Category_ID = tblLookUp_Category.Category_ID 
        Where tblClient_Category.Category_ID = ".$selectedcategory." 
        GROUP BY Surname, Forenames, Client_ID ASC";
    2nd Query...

    Code:
        SELECT lc.Category 
        FROM tblClient AS c 
        INNER JOIN tblClient_Category AS cc ON cc.Client_ID = c.Client_ID 
        INNER JOIN tblLookUp_Category AS lc ON lc.Category_ID = cc.Category_ID 
        WHERE c.Client_ID = $id;
    Many Thanks,
    Graham
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    i'm pretty sure i can combine those queries into what you want, but there's something wrong with your first query -- it shouldn't have a GROUP BY clause

    if i understand what you're doing with the first query, you want to provide a category id, and then pull out all the clients in this category, correct?

    (you also don't need the tblLookUp_Category table)

    let me know how this works for you --
    Code:
    SELECT tblClient.*
      FROM tblClient_Category 
    INNER 
      JOIN tblClient 
        ON tblClient.Client_ID = tblClient_Category.Client_ID 
     WHERE tblClient_Category.Category_ID = ".$selectedcategory."
    if that's okay, then i'll show you how to incorporate the second query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    5
    Hi There,

    Yes, that is what I'm after, doing with that first query, and your revised query does do the job too

    Your help is much appreciated
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    okay, here you go --
    Code:
    SELECT tblClient.*
         , GROUP_CONCAT(tblLookUp_Category.Category) AS categories
      FROM tblClient_Category 
    INNER 
      JOIN tblClient 
        ON tblClient.Client_ID = tblClient_Category.Client_ID 
    INNER
      JOIN tblClient_Category AS others
        ON others.Client_ID = tblClient.Client_ID
    INNER
      JOIN tblLookUp_Category
        ON tblLookUp_Category.Category_ID = others.Category_ID      
     WHERE tblClient_Category.Category_ID = ".$selectedcategory."
    GROUP
        BY tblClient.Client_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    5
    That's Fab! Thank you very much!

    Graham

IMN logo majestic logo threadwatch logo seochat tools logo