SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join on two different fields

    Is it possible to make a join with 2 ON statements? Like this:

    Code:
    SELECT *
    FROM table1 JOIN table2
    ON table1.field1 = table2.field1 AND ON table1.field2 = table2.field2
    WHERE ...

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can have a join condition involving two fields

    Code SQL:
    SELECT table1.*, table2.*
    FROM table1 JOIN table2
    ON table1.field1 = table2.field1 AND table1.field2 = table2.field2
    WHERE ...

    Or you can join to two different records in another table

    Code SQL:
    SELECT t1.*, t2.*, t2a.*
    FROM table1 t1
    JOIN table2 t2 ON t1.field1 = t2.field1
    JOIN table2 t2a AND t1.field2 = t2a.field2
    WHERE ...

  3. #3
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Philip, thanks for your reply

    I have two questions:
    1. First option: let's say that I have to fields in the first table that are joined with the same field on the second table:

    Code:
    ...
    ON table1.field1 = table2.field1 AND table1.field2 = table2.field1
    ...
    and I then take an information from the second table, let's say a value from field3. How can I distinguish them using PHP?

    PHP Code:
    $row['field3'
    will only contain the value of field3 that comes out from the join of table1.field1 and table2.field1 (and not from the join of table1.field2 and table2.field1), right? Maybe in this case $row becomes an array?

    2. Could you explain what the second code that you wrote does? How is it different from the first one?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    Is it possible to make a join with 2 ON statements? Like this:
    yes it is, but not quite like that -- there should be only one ON keyword, not two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by D3V4 View Post
    Code:
    ...
    ON table1.field1 = table2.field1 AND table1.field2 = table2.field1
    ...
    Why would you do that!?
    If table1.field1 equals table2.field1 and table1.field1 equals table2.field1 it follows that table1.field1=table1.field2, in which case one of them is redundant, or you if it's not you should write

    Code:
    ...
    ON table1.field1 = table1.field2 AND table1.field1 = table2.field1
    ...
    and not abuse table2 to check if two fields in table1 have the same value

    @PhilipToop: please don't use SELECT *, but rather select all the fields you want to have; SELECT field1, field2, field3, etc ...
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rémon, there is no "abuse" of table2 if it is needed as a foreign key lookup validation of the values in table 1 (which, as you pointed out, would be equal)

    and once again, we find ourselves chasing our own hypothetic tails, all because the original poster used fake table/column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @PhilipToop: please don't use SELECT *, but rather select all the fields you want to have; SELECT field1, field2, field3, etc .
    @ScallioXTX: Just keeping the original syntax of @D3V4.

    @D3V4:

    First option: let's say that I have to fields in the first table that are joined with the same field on the second table:
    Are you talking about a scenario such as

    table1 with fields, id, title, keyid1, keyid2
    table2 with fields id, name

    keyid1 and keyid2 both refer to an entry in table2

    Using a form of my second sql statement

    Code SQL:
    SELECT t1.id, t1.title, t2.name name1, t2a.name name2
    FROM table1 t1
    JOIN table2 t2 ON t2.field1 = t1.keyid1
    JOIN table2 t2a AND t2a.field2 = t1.keyid2
    WHERE

    In your PHP code you would access $row['name1'] and $row['name2']

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry the final SQL should have read

    Code SQL:
    SELECT t1.id, t1.title, t2.name name1, t2a.name name2
    FROM table1 t1
    JOIN table2 t2 ON t2.id = t1.keyid1
    JOIN table2 t2a AND t2a.id = t1.keyid2
    WHERE

  9. #9
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Why would you do that!?
    Because the two fields in the first table may have different values and therefore be connected to a different field in the second table.

    Quote Originally Posted by r937 View Post
    and once again, we find ourselves chasing our own hypothetic tails, all because the original poster used fake table/column names
    You are right ^^ To make things clearer, the fields of the first table are employee and clerk, while the fields of the second table are boss and subordinate.
    Employee and clerk can have two different values, but both of them are connected to the subordinate field in the second table.

    Quote Originally Posted by PhilipToop View Post
    @D3V4:
    Are you talking about a scenario such as

    table1 with fields, id, title, keyid1, keyid2
    table2 with fields id, name

    keyid1 and keyid2 both refer to an entry in table2
    I've explained the situation right before this quote

  10. #10
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    Sorry the final SQL should have read

    Code SQL:
    SELECT t1.id, t1.title, t2.name name1, t2a.name name2
    FROM table1 t1
    JOIN table2 t2 ON t2.id = t1.keyid1
    JOIN table2 t2a AND t2a.id = t1.keyid2
    WHERE
    A question that just came up to my mind: if t1 is the first table and t2 is the second one, what's t2a?

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Each record on table1 has a column keyid1 and a column keyid2. Both of these columns refer to a record in table2. When we retrieve records from table1 we want to retrieve the corresponding record from table2 for keyid1 and the corresponding record from table2 for keyid2.

    To achieve this we have two instances of table2 (there is physically only one table), and join one instance on keyid1 and the second instance on keyid2.

  12. #12
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    Each record on table1 has a column keyid1 and a column keyid2. Both of these columns refer to a record in table2. When we retrieve records from table1 we want to retrieve the corresponding record from table2 for keyid1 and the corresponding record from table2 for keyid2.

    To achieve this we have two instances of table2 (there is physically only one table), and join one instance on keyid1 and the second instance on keyid2.
    Very clear explanation, thank you very much!

    I've tried implementing your SQL but it doesn't work for me. Just to make things clearer, I should use the real table names in place of table1 and table2 in your code, or in place of t1 and t2?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    I should use the real table names in place of table1 and table2 in your code, or in place of t1 and t2?
    test both, and see what happens!!

    after all, the confusion over actual names began right in post #1, so clearing it up is actually your job

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

  14. #14
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    test both, and see what happens!!

    after all, the confusion over actual names began right in post #1, so clearing it up is actually your job

    When they hire you as a SQL consultant do you reply this way too?

    Flame apart, I've tried all the combinations and none of them worked. Then I realized that there was a mistake in Philip's code (I used ON instead of AND) and everything worked

  15. #15
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry typo.

  16. #16
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    Sorry typo.
    Don't worry, thanks a lot for your help!

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by D3V4 View Post
    When they hire you as a SQL consultant do you reply this way too?
    of course not

    you get what you pay for

    and my advice here at sitepoint has always come with a money-back guarantee
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    of course not

    you get what you pay for

    and my advice here at sitepoint has always come with a money-back guarantee
    Lol xD next time give me your paypal address, just in case i need a better service


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
  •