Selecting from two tables...help?

Hiccup

I want the "pid" for each record in my "suspects" table where they are a prospect and they have not met with me yet, (in the "notes" table as notes.result !='Got Meeting'.)

I've tried this:

$sql = "SELECT DISTINCT notes.pid FROM notes, suspects where notes.pid = suspects.pid and notes.result != 'Got Meeting' and suspects.status = 'prospect' ORDER BY suspects.last_contact ASC"

This produces all my records who are prospects in the suspects table, but doesn't exclude records where I've met with them already. (notes.result != 'Got Meeting').

Can anyone tell me how to do this?

Lars_Berg

select pid from suspects
where status = 'prospect'
and pid not in (select pid
from notes
where result = 'Got Meeting')
order by last_contact

Hiccup

Error

SQL-query :

SELECT pid
FROM suspects
where status = 'prospect'
and pid
not 
in (

select pid
FROM notes
where result = 'Got Meeting'
)
ORDER BY last_contact

MySQL said:

You have an error in your SQL syntax near 'select pid FROM notes where result = 'Got Meeting') ORDER BY last_contact LIMIT ' at line 1

Any ideas?

Lars_Berg

Oh, you are stuck with that crappy dbms Mysql.

In many cases (but not all) not in can be rewritten as an outer join.

select pid from suspects left join
notes on suspects.pid = notes.pid
and result = 'Got Meeting'
where status = 'prospect'
and notes.pid is null
order by last_contact