Specific Join Query Problem

[gordonfreeman83]

Hi all, I would appreciate any help at all.

 

I'm trying to do a specific query on two tables, and am not gettin the results im expecting.

 

We have two tables, Accounts (client information, ID etc) and Notes (notes against accounts with matching ID ). I want to find all the accounts who haven't had a note made against them for the last 6 months.

 

After reading the fine manual i thought this might do it:

 

select accounts.name, accounts.phone_office from accounts left join notes on accounts.id = notes.id where notes.id is NULL and notes.date_created between '2006-10-15' and '2007-03-08';

 

Using the notes.id is NULL works as the manual says, finding all the accounts.id that have no corresponding notes.id and thus no notes against them, but it comes back with no result when combined with the note date where clause, even though there are many match

 

Have i made a mistake in my logic here, or got the query completely wrong? Thanks in advance for any help!

 

Peter

 

[artacus]

You have conflicting WHERE clauses... note date CANT be between that range when it doesn't exist. Also, note.id is not going to be the FK relating to account.id

SELECT a.name, a.phone_office 
FROM accounts AS a
LEFT JOIN notes AS n ON  as.id = n.account_id 
     AND n.date_created > DATE_SUB(NOW(),INTERVAL 6 MONTH)
WHERE n.id IS NULL;