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

    Join Date
    Jun 2013
    Posts
    16
    Rep Power
    0

    JOINS and getting the name a foreign key points to


    I have 3 tables:

    PROGRAMS
    id auto_inc
    name
    owner
    etc...

    CLIENTS
    id auto_inc
    name
    address
    etc..

    PROGRAMS_CLIENTS
    id auto_inc
    prog_id foreign key pointing to PROGRAMS.id
    client_id foreign key pointing to CLINTS.id


    I want to run a query that gets * rows from PROGRAMS_CLIENTS for a given prog_id but instead of showing client_id I want to display the actual client name that it references in the CLIENTS table. I have tried several querys but with out success

    Code:
    $sql= "SELECT * FROM programs_clients INNER JOIN clients ON programs_clients.client_id = clients.id
                 WHERE program_id = 15";

    What am I doing wrong? thx
  2. #2
  3. Turn left at the third duck
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Location
    Nelson, NZ
    Posts
    112
    Rep Power
    93
    You need to specify the fields you want to select in each joined table. This is how you do it.
    Note that p and c are aliases for the two tables, specified in the FROM. We use them in the SELECT part of the statement to target a particular table.

    SELECT p.`id`, p.`client_id`, c.`name`
    FROM programs_clients p
    JOIN clients c ON programs_clients.client_id = clients.id
    WHERE program_id = 15
    Edit:
    You could also make JOINs easier by renaming your id column in CLIENTS client_id, the same as in the associative table (same for the programs table). Then the JOIN would look like this:

    SELECT id, client_id, name
    FROM programs_clients
    JOIN clients USING (client_id)
    WHERE program_id = 15
    At this stage we don't need the table aliases anymore because none of the field names are ambiguous. (You only need these aliases in the SELECT line for ambiguous field names, so you could also simplify the first example I gave you.)
    Last edited by ragax; August 1st, 2013 at 04:07 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    16
    Rep Power
    0
    thanks! I will give this a shot. I have a lot of fields in the programs_clients tbl and was hoping I could just use * for all of them. But I will type them out and try what you recommend.

    Originally Posted by ragax
    You need to specify the fields you want to select in each joined table. This is how you do it.
    Note that p and c are aliases for the two tables, specified in the FROM. We use them in the SELECT part of the statement to target a particular table.



    Edit:
    You could also make JOINs easier by renaming your id column in CLIENTS client_id, the same as in the associative table (same for the programs table). Then the JOIN would look like this:



    At this stage we don't need the table aliases anymore because none of the field names are ambiguous. (You only need these aliases in the SELECT line for ambiguous field names, so you could also simplify the first example I gave you.)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by ragax
    We use them in the SELECT part of the statement to target a particular table.
    actually, you should use the table aliases (or tables names, if you don't use aliases) on ~all~ columns in the query, not just in the SELECT clause

    looking at your query, there's no easy way to tell which table program_id belongs to


    Originally Posted by ragax
    At this stage we don't need the table aliases anymore because none of the field names are ambiguous.
    wrong

    you've forgotten to qualify the client_id in the SELECT clause, so you ~will~ get an "ambiguous column" error

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Turn left at the third duck
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Location
    Nelson, NZ
    Posts
    112
    Rep Power
    93
    Hi r937,

    wrong
    you've forgotten to qualify the client_id in the SELECT clause, so you ~will~ get an "ambiguous column" error
    Nope, sorry, with all respect for your expertise, I do believe you are mistaken on this one.

    I have this pattern in over a hundred queries.

    Part of the USING syntax is that you don't need to specify the origin of the "used field" in the SELECT part of the statement. One of the reasons I suggested it: more compact in more ways than one.

    Wishing you all a fun weekend.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    well, whaddya know

    i guess it's because i've never tested it, since i have an intense dislike of USING (because it requires the same name in both tables, which goes against my column naming standards), almost as much as i hate NATURAL joins, which are even worse

    thanks for setting me straight so politely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Turn left at the third duck
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Location
    Nelson, NZ
    Posts
    112
    Rep Power
    93

    L


    Hey, no worries. It's probably harder for you, using SQL in various brands of databases, to be familiar with all the local SQL idiosyncracies---different idioms in mySQL, Oracle etc. The only db I use is MySQL (very very far from expert level) so there's no "noise" around the bits of syntax that I use regularly.

    Kindest regards from the other side of the Commonwealth (or something like that).

IMN logo majestic logo threadwatch logo seochat tools logo