jeff5656 Posted December 9, 2010 Share Posted December 9, 2010 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... Link to comment https://forums.phpfreaks.com/topic/221129-a-query-to-exclude-certn-fields-that-match/ Share on other sites More sharing options...
JohniMo Posted December 9, 2010 Share Posted December 9, 2010 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 Link to comment https://forums.phpfreaks.com/topic/221129-a-query-to-exclude-certn-fields-that-match/#findComment-1144990 Share on other sites More sharing options...
jeff5656 Posted December 9, 2010 Author Share Posted December 9, 2010 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)? Link to comment https://forums.phpfreaks.com/topic/221129-a-query-to-exclude-certn-fields-that-match/#findComment-1145033 Share on other sites More sharing options...
fenway Posted December 9, 2010 Share Posted December 9, 2010 With IS NULL and LEFT JOIN, you'll only get the rows without matches in the other table. Link to comment https://forums.phpfreaks.com/topic/221129-a-query-to-exclude-certn-fields-that-match/#findComment-1145055 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.