I've been grappling with a problem for some time, checking thread after thread on S/O to no avail. I should mention I'm building a SQL query for use in Crystal Reports.
I am attempting to join two tables together that MAY be joined via a junction table - this can be done via UNIONs but the table then links to itself and it can become very messy accounting for all potential scenarios.
Let's say we have three tables: t1, t2 and t3.
I'm after a quick and reusable way to say "LEFT JOIN t1 & t2 directly OR LEFT JOIN via table3".
Here's a really basic knock-up of what I'm looking for:
SELECT t1.ID, t2.ID
FROM t AS t1
LEFT JOIN (
t AS t2 ON t1.ID = t2.parentID OR (
t AS t2 ON t1.ID = t3.ID1 AND t3.ID2 = t2.ID ))
Is this at all possible without ending up with two versions of t2? I'm hoping there's a function out there I'm just unaware of.
EDIT: the direct t2 join is to a 'parentId' column on t2 and t1 & t2 are aliases of the same column.
Here's my desired output:
+-------+--------+--------+-------+-------------+
| t1.ID | t3.ID1 | t3.ID2 | t2.ID | t2.parentID |
+-------+--------+--------+-------+-------------+
| 001 | NULL | NULL | 004 | 001 |
| 002 | 002 | 003 | 003 | NULL |
| 003 | NULL | NULL | NULL | NULL |
+-------+--------+--------+-------+-------------+
This probably close to your request. But if t3.ID1 is on the same domain than t2.ID you can have problems
SELECT t1.ID, t2.ID
FROM t1
LEFT JOIN t3
ON t1.ID = t3.ID1
LEFT JOIN t2
ON t1.ID = t2.ID
OR t3.ID2 = t2.ID
So maybe you can include some validations
SELECT S.ID1, COALESCE(S.ID2, T2.ID)
FROM
( SELECT t1.ID as ID1, t2.ID as ID2
FROM t1
LEFT JOIN t2
ON t1.ID = t2.ID
-- This step look weird if t1.ID = t2.ID you just print same ID twice.
) as S
LEFT JOIN t3
ON S.ID1 = t3.ID1
AND S.ID2 IS NULL -- ONLY JOIN IF NOT MATCH ON FIRST QUERY
LEFT JOIN t2
ON t2.ID1 = t2.ID