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

    Join Date
    Jul 2014
    Posts
    24
    Rep Power
    0

    Assistance with Joins in mysql


    Hi All,

    This is a really simple question about Joins, I'm trying to get a list of Categories a client is assigned to, there's a middle table between the two breaking the many-many relationship called tblClient_Category. Here is my SQL:

    [MYSQL]SELECT tblLookup_Category.Category_ID, tblLookup_Category.Category_Name, tbl_Client.Client_ID, tbl_Client_Category.Client_ID, tbl_Client_Category.Category_ID
    FROM ((tblLookup_Category AS lc
    INNER JOIN tblClient ON tblClient_Category.Client_ID = tblClient.Client_ID)
    INNER JOIN tblLookup_Category ON tblClient_Category.Category_ID = tblLookup_Category.Category_ID)
    WHERE tbl_Client_Category.Client_ID = 101[/MYSQL]

    As it is at the moment I'm getting an error

    MySQL said: Documentation

    #1066 - Not unique table/alias: 'tblLookup_Category'
    Any help appreciated.

    Many Thanks,
    Graham
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by Graham_K
    I'm trying to get a list of Categories a client is assigned to...
    this is what i call the "filter" condition -- it filters the rows being returned to the join

    one very strong suggestion i have for writing joins is to put the table with the filter condition first in your FROM clause

    in other words, get the Client row for the desired (filtered) client, and join the other tables only to that row

    this is conceptually different from doing a three-table join and then throwing away all the joined rows that don't belong to the desired (filtered) client

    that said, a good database optimizer will re-arrange the joins no matter how you write them so that it can apply the filter condition first -- nevertheless, it's good practice to write your joins in the same way that makes sense for performance

    another tip -- don't put both join columns into the SELECT list if your join conditions force them to be equal

    finally, in this case you don't really need the tblClient table, because you aren't selecting any columns for it, but typically you would, so i've invented the client_name column to illustrate

    (oh, and by the way, sticking "tbl" on the front of your table names is useless noise -- imagine if we did this in english: nounParis prepIn artThe nounSpring
    Code:
    SELECT c.Client_ID
         , c.Client_Name
         , lc.Category_ID
         , lc.Category_Name
      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 = 101
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    24
    Rep Power
    0
    You Sir, are a true Gentleman1

    Thankyou

IMN logo majestic logo threadwatch logo seochat tools logo