Left Join; Only if there is at least one matching record

Question

I need to join two pairs of tables. If there is an ID in Table1 that can also be found in Table3 I need to join the tables. If there is no matching ID from Table1 in Table3, I need to not join the tables.

Ex.

If there is at least one id in Table1 in Table3; do something that is effectively this:

 SELECT * FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.ID = t2.ID LEFT JOIN Table3 AS t3 ON t1.ID = t3.ID LEFT JOIN Table4 AS t4 ON t3.ID = t4.ID

If there are no IDs that match between Table1 and Table3; do something that is effectively this:

 SELECT * FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.ID = t2.ID

You say " If there is no matching ID from Table1 in Table3, I need to not join the tables." but then go on to say you need a join of table1 and table2, in that case. And in what way are the tables "two pairs"? It's not at all clear what you want... — The Archetypal Paul
– The Archetypal Paul, Commented Jan 25, 2016 at 19:37
@TheArchetypalPaul sounds like they only want the fields from table 3 and 4 in the result set if they're not all NULL — JamieD77
– JamieD77, Commented Jan 25, 2016 at 19:44
It's not clear what you want. @JamieD77's guess is a reasonable one. OP, can you confirm that that is what you're after (not having those columns), or are you interested in some change in the rows as well? — user743382
– user743382, Commented Jan 25, 2016 at 19:46
@JamieD77 is correct. Apologies for my unclear-ness; I need to join Table3 and Table4 to Table1 and Table2 only if there is a match of IDs from Table1 in Table3. The code snippets are representative of what I am after. The 'two pairs' was simply referring to Table1 and Table2 always being together as well as Table3 and Table4 always being together. Thank you all for replying and coming to my aid. — userouseParticularouse
– userouseParticularouse, Commented Jan 25, 2016 at 19:58
is this something you're trying to do in a stored proc/function/view? — JamieD77
– JamieD77, Commented Jan 25, 2016 at 20:00

Tab Alleman · Accepted Answer · 2016-01-25 19:57:04Z

Just translating your question into SQL, you can do this:

IF EXISTS(SELECT * FROM Table1 T1 INNER JOIN Table3 T3 ON T1.ID=T3.ID) SELECT * FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.ID = t2.ID LEFT JOIN Table3 AS t3 ON t1.ID = t3.ID LEFT JOIN Table4 AS t4 ON t3.ID = t4.ID ELSE SELECT * FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.ID = t2.ID

Collectives™ on Stack Overflow

Left Join; Only if there is at least one matching record

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related