1

So I have 2 tables. Table B has a foreign key relationship with Table A.

For example.

Table A
id        column 1        column 2
1           some            thing
2           foo             bar
3           hello           world
4           this            that
5            x                y

Table B
id        Table A id        testcolumn
1             5               blah
2             5               blah
3             2               aggg
4             2               aggg
5             4                a
6             4                b
7             4                c

What I want is to select all of the elements from table A where there is no foreign key match in table B. In the case of the example I would want to select the row with id of 1 and 3 from table A. How would I accomplish that in a MS SQL database?

wmoskal
  • 295
  • 1
  • 4
  • 15
  • This is a special case of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/q/367863/3404097). PS Please in code questions give a minimal reproducible example--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Also show what you tried & how you are stuck, please don't ask us to write your code. – philipxy Oct 25 '19 at 20:23

1 Answers1

7

You can use an anti join:

select a.*
from a
left join b on a.id = b.table_a_id
where b.table_a_id is null
The Impaler
  • 45,731
  • 9
  • 39
  • 76