Help with complex query

[TheDust]

I have a search query that searches our client database and returns results for most values entered in the search form. The query looks like this:

SELECT * FROM clients c
	LEFT JOIN referenceTypes rt ON c.referenceTypeID=rt.referenceTypeID 
	LEFT JOIN clientsContacts cc ON c.clientID=cc.clientID 
	LEFT JOIN contacts co ON cc.contactID=co.contactID 
	LEFT JOIN clientsManagers cm ON c.clientID=cm.clientID 
	LEFT JOIN users u ON cm.userID=u.userID

This query returns 'u.userID' as NULL for many of the returned records. What I want to know is how to return ONLY results where the column 'u.userID' is NULL. When I tack on "WHERE u.userID=null", the query executes, but no values are being returned. This is because the table 'clientsManagers' allows for one client to have multiple managers and this is how the 'users' table is then joined into the query. So, if a client doesn't appear in the 'clientsManagers' table, the 'u.userID' column doesn't even exist until this query is returned and the 'u.userID' field just defaults to null at that point.

Is there a way to select all clients that do not have any linked records in table 'clientsManagers'?

 

[cLFlaVA]

does this help?

SELECT * FROM clients c
    LEFT JOIN referenceTypes rt ON c.referenceTypeID=rt.referenceTypeID
    LEFT JOIN clientsContacts cc ON c.clientID=cc.clientID
    LEFT JOIN contacts co ON cc.contactID=co.contactID
    LEFT JOIN clientsManagers cm ON c.clientID=cm.clientID
    LEFT JOIN users u ON cm.userID=u.userID [blue]AND
              u.userID IS NULL[/blue]

*cLFlaVA
----------------------------
[tt]insert funny quotation here.[/tt]

 

[TheDust]

That did it. I was using "u.userID=null" instead of "u.userID IS NULL". Simple mistake. I'm glad it was easy to fix up. Thanks!!