Finding records with no join

[Ari Maniatis]

I have an SQL question for which I'd be grateful for any help. I hope this is an appropriate place to post. In short, I have a table (Tasks) which are related to (Person) via a many-many join table (Task_Person). I need to find all Tasks which are not joined at all (have no Task_Person records in the join).

I know how to do this with HAVING and a count, but this doesn't really help in my case. I have a query with a fair amount of logic in it already. Essentially I want to add to that a clause in the where:

WHERE COUNT(tp.ID) = 0 OR ...

Of course, I know that I can't put this into the where clause, so another solution is needed.

Thanks very much in advance
Ari Maniatis



-----


Following is a simplified version of the current SQL;

SELECT *,
GROUP_CONCAT(assignedPerson.FirstName ORDER BY assignedPerson.FirstName SEPARATOR ', ') AS AssignedName, tpMe.IsUnread AS unreadForMe, tpMe.IsAssigned AS assignedToMe

FROM Task AS t
LEFT JOIN Task_person AS tpMe ON (t.ID = tpMe.Task_ID AND tpMe.Person_ID = -1)
LEFT JOIN Task_person AS tp ON (t.ID = tp.Task_ID AND tp.IsAssigned = 1)
LEFT JOIN Person AS assignedPerson ON (assignedPerson.ID = tp.Person_ID AND assignedPerson.Permissions = 100)
WHERE tpMe.IsUnread = 1 OR (Status < 4 AND t.Action_Datetime < '1970-01-22 00:00:00' AND tp.Person_ID IN (-644))
GROUP BY t.ID