I can't come up with a working SQL code that gives me Outcome_Table. Need to LEFT JOIN 2 columns from Table_B to Table_A where IF A.srid is null, it joins by odid ELSE joins by srid
Table_A
| Column A.* | A.srid | A.odid |
| Cell 1 | | 111 |
| Cell 2 | | 222 |
| Cell 3 | qwe123 | 333 |
| Cell 4 | q1w2e3 | 444 |
Table_B
| Column B.* | B.srid | B.odid |
| Cell 1 | qwe123 | 555 |
| Cell 2 | q1w2e3 | 666 |
| Cell 3 | | 111 |
| Cell 4 | 321ewq | 777 |
| Cell 5 | qwe123 | 555 |
Outcome_Table
| Column A.* | A.srid | A.odid | B.srid | B.odid |
| Cell 1 | | 111 | | 111 |
| Cell 2 | | 222 | | |
| Cell 3 | qwe123 | 333 | qwe123 | 555 |
| Cell 4 | q1w2e3 | 444 | q1w2e3 | 666 |
I've tried this SQL query:
SELECT Table_A.* , Table_B.odid, Table_B.srid FROM Table_A LEFT JOIN Table_B ON Table_A.srid = Table_B.srid OR Table_A.odid = Table_B.odid
It appears to work extremely slow and not even sure/can't confirm if the results were true. Cannot update my Outcome_Table in Power BI with this query. Please, help me getting desired result in the Outcome_Table example.
You can do with union all for two query like this below
SELECT Table_A.* , Table_B.odid, Table_B.srid FROM Table_A LEFT JOIN Table_B ON Table_A.srid = Table_B.srid where Table_A.srid is not null Union all SELECT Table_A.* , Table_B.odid, Table_B.srid FROM Table_A LEFT JOIN Table_B ON Table_A.odid = Table_B.odid where Table_A.srid is null
