SQL - Need to conditionally join based on value in TableB column

Question

I need to know the rows in TABLE A that have join records in TABLE B based a column value in TABLE B, but I also need to return rows in which a row in TABLE A has no match in TABLE B.

It seems like I need a LEFT JOIN and a LEFT OUTER JOIN, so I'm not sure what to do there. I understand how to do each, but don't understand how to do them together.

The schema looks like:

TABLE_A
pk

TABLE_B
pk
a_fk
some_value

I need the joined rows where Table_A has no join record in Table_B OR Table_A has a join record row in Table_B (it can have many) in which some_value does not equal "thisValue"

Thanks.

check this: https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server — Bharath K, Jun 08 '17 at 15:42
If add sample data and desired result, will be easy to understand your requirement — Oto Shavadze, Jun 08 '17 at 15:42

MarkD · Answer 1 · 2017-06-09T08:51:54.350

1

A Left join is a left outer join. Outer joins preserve one of the tables which is what you are after so good guess.

SELECT *
FROM Table A
LEFT JOIN Table B
ON TableA.Column = TableB.Column
AND B.SomeValue <> 'ThisValue'

All of the rows with a match will have the B information populated all of those without will have nulls in the B data

edited Jun 09 '17 at 08:51

answered Jun 08 '17 at 15:56

MarkD

1,043
12
18

@mstrom I have added an extra join conditional that should fit your requirements – MarkD Jun 09 '17 at 08:52

SQL - Need to conditionally join based on value in TableB column

1 Answers1