SQL join table with two 'ON' keyword ???

Question

Hi everyone, I have two tables tbl_a and tbl_b and their fields are

tbl_a
-----
a_id a_name a_surname a_c_id a_d_id

tbl_b
-----
b_id b_name b_phone b_c_id b_d_id

I want to join these two table like this:

select *from tbl_a join tbl_b on tbl_a.a_c_id=tbl_b.b_c_id AND tbl_a.a_d_id=tbl_b.b_d_id where tbl_a.id>15;

As you can see i want to use two keys for ON keyword,is there anyway to do this? Thanks for advance...

Answer 1

Sql server allows this. I can speak for other implementations.

Answer 2

The way you put it should work fine.

In MySQL you can set multiple join constraints by using AND.

Answer 3

it works fine but use alias instead of long tablename

select * from tbl_a a 
         join tbl_b b on a.a_c_id=b.b_c_id AND a.a_d_id=b.b_d_id 
         where a.id>15;

Answer 4

Your query is right there is no need for putting extra On keyword AND keyword do work of join in on two diffirent coluns. Just put the bracket to understand properly.

select *from tbl_a join tbl_b on 
(
  tbl_a.a_c_id=tbl_b.b_c_id 
  AND 
  tbl_a.a_d_id=tbl_b.b_d_id 
)
where tbl_a.id>15;