Reputation: 29
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|
Upvotes: 0
Views: 30
Reputation: 1884
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
Upvotes: 2