SQL query (get it!?)

[mastubbs]

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

[Barand]

After running the query, what does mysql_error() tell you?

[kicken]

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'

[mastubbs]

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.