how to query this one.

[stephan.gerlach]

Hi,

I am trying to create a query which can do the following.

I have two tables called client and notes. Notes has the fields id,client,date,text.

I want to run a query that returns a list of clients that have no notes added since a specific date. So only clients where the last note is before the desired date will be returned.

How can I do this? Any help?

[Mindzai]

Assuming client table as a field called id which corresponds to notes.client:


SELECT * FROM clients AS c
INNER JOIN notes AS n
ON n.client = c.id
WHERE n.date < '$date'


where $date is your desired date.

That will return all clients and their notes where the note date is before you desired date. So if a client has 1 note before you desired date and one note after, 1 row will be returned with the before note. Is this what you need?