Join on two different fields

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


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

You can have a join condition involving two fields

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

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

Hi Philip, thanks for your reply :slight_smile:

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:

...
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?


$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?

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

yes it is, but not quite like that – there should be only one ON keyword, not two

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


...
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, 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

@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

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’]

Sorry the final SQL should have read

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

Because the two fields in the first table may have different values and therefore be connected to a different field in the second table.

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.

I’ve explained the situation right before this quote :wink:

A question that just came up to my mind: if t1 is the first table and t2 is the second one, what’s t2a?

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! :slight_smile:

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?

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

:cool:

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

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 :slight_smile:

Sorry typo.

Don’t worry, thanks a lot for your help! :slight_smile:

of course not

you get what you pay for :wink:

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 :stuck_out_tongue: