MySQL: Select with SubQuery

Question

I need help with a conditional select/sub query statement.

I have three tables, Tab1,Tab2 and Tab3. Each table has an ID column.

Tab1:

id | code
1  | 1
2  | 2
3  | 1

Tab2:

id | results
1  | data1
2  | data2
3  | data3

Tab3:

id | results
1  | data1
2  | data2
3  | data3

If Tab1.code is 1 for id1, than I want a select statement to return all results for id1 inside Tab2. If Tab1.code is 2 for id1, than I want all the results for id1 returned from Tab3.

I’ve been playing around with multiple sub queries and cannot get it to work.

Here is a fiddle with the basic structure: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=044b04ea153b15fc3ce17cefc292f6bc

Answer 1

If you have only 2 sub-tables 2 and 3, you can try this solution:

SELECT t1.id, t1.code, t2.results
FROM Tab1 t1
JOIN Tab2 t2
ON t1.code = 1 AND t1.id = t2.id
UNION
SELECT t1.id, t1.code, t3.results
FROM Tab1 t1
JOIN Tab3 t3
ON t1.code = 2 AND t1.id = t3.id