a query to exclude certn fields that match

[jeff5656]

I have 2 table , pts and p_list.  pts has a field called v3 and p_list has a field called mrn.

In the query I want to run all the records from pts but exclude records that match mrn in p_lis table.

I'm sure there is something like JOIN, ut I can't figure it out.

This didn't work

 

$qq = "select * from pts, p_list WHERE pts.v3 != p_list.mrn 
AND pts.v1 like '$lname%' AND pts.user_id = '". $use['id'] ."' ";

 

That didn't exclude records that matched v3 to mrn...

[JohniMo]

i think you're looking for a LEFT OUTER JOIN. Basically you want all records in pts EXCEPT the records where (pts.v3 = p_list.mrn), yes? An outer join matches up records then pulls out the ones that missed (on the NULLS).

 

SELECT * FROM pts LEFT OUTER JOIN p_list ON pts.v3 = p_list.mrn WHERE pts.v3 IS NULL

 

I think that's it... 

J

 

[jeff5656]

By adding the outer join and putting in the WHERE pts.v3 IS NULL, I don't get ANY matches now.

 

Does that mean the content of the v3 field has to be blank (because it isn't)?

[fenway]

With IS NULL and LEFT JOIN, you'll only get the rows without matches in the other table.