joining of tables based on the value of the fields

Question

I was struck with a problem in joining of tables, there is a condition regarding the joining of two tables.. I've three tables let us assume it table1,table2 & table3,

table1
+---+
|id |
+---+

table2
+---------------+
|id | table1_id |
+---------------+

table3
+----------------------------+
| id | table1_id | table2_id |
+----------------------------+

Now, my master table is "table3", I need to join the master table with table1 & table2 in such a way that if the value of table2_id exists in the table3 then table2 should be joined with table2_id & similarly if table1_id exits then table1 will be joined with the table1_id,for eg: the entry into table3 is in this way

+----------------------------+
| id | table1_id | table2_id |
|  1 |     1     |     0     | 
|  2 |     0     |     1     |
+----------------------------+

for the value of id = 1,
table1_id exists & table2_id is zero, so table1 should be joined,
for the id = 2,
table2_id exists & table1_id is zero, so table2 should be joined,
if there is a case that both exists then table2 should be given the priority i.e, the table2 will be joined, can anyone make me out of this prb pls..

Answer 1

you can try to make procedure in which you can put the conditions and execute the query as per your condition.

Answer 2

You can probably do it using a LEFT JOIN and then sorting out the resulting columns required using a CASE statement.

As an example you could do something like this. Note you would need to repeat the CASE statement for each field you want to bring back.

SELECT table3.id, CASE table2.id IS NULL THEN table1.field ELSE table2.field END AS field
FROM table3
LEFT OUTER JOIN table2 ON table3.table2_id = table2.id
LEFT OUTER JOIN table1 ON table3.table1_id = table1.id