Conditional join mysql

Question

I have two types product. I create two tables for those (t1,t2). I also have one transaction table (t3) I want to join t3 with t1 but if there is no match then join t2

t1

|p_id|p_description|color|

|0001|   xyz       |blue |
|0002|   bcd       |red  |

t2

|p_id|p_description|weight||size|type|

|e-01|   xmay       |3.25|  | 50| s |
|s-02|   cmay       |2.32|  | 25| x |

t3

|trn_id|  p_id |cost|

|  1   |  0001 | 150|
|  2   |  s-02 | 225|

Answer 1

You can use a UNION statement:

SELECT a.trn_id, a.p_id, a.cost, b.p_description
FROM t3 a
LEFT JOIN t1 b ON a.p_id = b.p_id
WHERE b.p_id IS NOT NULL
UNION SELECT a.trn_id, a.p_id, a.cost, b.p_description
FROM t3 a
LEFT JOIN t2 b ON a.p_id = b.p_id
WHERE b.p_id IS NOT NULL

This code will get you a table with all results from t3 coincident with t1, and after this, on the same table, results from t3 coincident with t2