Jump to content

SQL query (get it!?)


mastubbs

Recommended Posts

Hi All,

 

So i have two tables, 'addobs' and 'patients'. In patients one variable is 'ward.  I am using an SQL query as follows to  extract data from both tables:

$Find_Query2 = mysql_query("SELECT patients.*, addobs.*
FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN
WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND addobs.par > 6 
AND NOT addobs.hidden = 'yes'
AND NOT patients.ward = 'dc'
order by addobs.par ASC");

However, there is a problem somewhere with this and i get no results returned (although there are records that fit the query). However, if i remove

AND NOT patients.ward = 'dc'

 then it works (except of course i get all records, whether ward is 'dc' or not) - but i want to only display records where ward is not 'dc'.  Interestingly, if i change that line to 

AND patients.ward = 'dc'

then it return all of the results where ward='dc'. So why does 'NOT' not work? Maybe something to do with the fact that that some of the values are NULL?

 

Any ideas would be much appreciated.

 

Thanks

 

Matt

Link to comment
Share on other sites

Maybe something to do with the fact that that some of the values are NULL?

Rows that have NULL for the ward would not match. In order to match a NULL value you have to use either IS NULL or IS NOT NULL, checking using = will always fail to match. You could use COALESCE to change nulls to an empty string for comparison.

AND COALESCE(patients.ward,'') != 'dc'
Link to comment
Share on other sites

Rows that have NULL for the ward would not match. In order to match a NULL value you have to use either IS NULL or IS NOT NULL, checking using = will always fail to match. You could use COALESCE to change nulls to an empty string for comparison.

AND COALESCE(patients.ward,'') != 'dc'

 

Ahh... i see. yep that worked. Thanks Kicken.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.