1
assignments:
id | prospectid
1  | 1
2  | 2
3  | 5

prospects:
id | name
1  | purple
2  | red
3  | blue
4  | orange
5  | green

I'm wanting to return a list of prospects where prospect.id does not exist under the prospectid column within assignments. In a nutshell, I want to return unassigned prospects.

So from the above example, it ought to return:

prospects:
3 | blue
4 | orange

My failed SQL:

SELECT * FROM prospects
WHERE prospects.id != `assignments`.prospectid
ditto
  • 5,429
  • 6
  • 48
  • 86

2 Answers2

3

You can use a NOT EXISTS clause

select * 
from prospects p
where not exists (select null from 
                  assignments a
                  where a.prospectid = p.id)
Raphaël Althaus
  • 58,557
  • 6
  • 89
  • 116
1

use LEFT JOIN

SELECT P.id, P.name
FROM Prospects P
LEFT JOIN assignments A
on P.id = A.prospectid
WHERE A.prospectid is NULL
radar
  • 13,100
  • 2
  • 23
  • 32