I try co create a SQL query with two inner joins and an if case. I create an example to explain what I mean:
| ID | Typ | Case |
|---|---|---|
| 123 | AAA | zzz |
| 124 | BBB | yyy |
| 125 | CCC | yyy |
| Typ1 | ID1 |
|---|---|
| AAA | 888 |
| BBB | 999 |
| CCC | 777 |
| ID2 | Result |
|---|---|
| 666 | 1 |
| 555 | 2 |
| 777 | 3 |
In words, the query should do:
Search in the first table for ID 125, so I get Typ CCC and Case yyy
If case is yyy then search in the second table for CCC in column Typ1, here I get the ID 777 and then search in the third table for 777 in column ID2 to get the result 3.
If case is not yyy then just show me the results of the first table.
The result should be:
| ID | Typ | Result |
|---|---|---|
| 123 | AAA | No match |
| 124 | BBB | No match |
| 125 | CCC | 3 |
I hope you can understand what I try to explain 🙂
>Solution :
You want to select data from the first table and only show data from the other tables where appropriate. So, outer join the other tables.
select t1.id, t1.typ, t3.result
from t1
left outer join t2 on t2.typ1 = t1.typ and t1.case = 'yyy'
left outer join t3 on t3.id2 = t2.id1
order by t1.id;