[RESOLVED] mySQL Query not working

frontporch

This should be an easy query, but I'm having a heck of a time figuring it out.

I am trying to join two tables as follows:

SELECT ServiceMemo.*, ServiceMemoWork.*
FROM ServiceMemo, ServiceMemoWork
WHERE ServiceMemo.completed = 'yes'
AND ServiceMemoWork.serviceMemoID = ServiceMemo.id
AND ServiceMemoWork.reason != 'No Service'

There is a one-to-many relationship b/w ServiceMemo and ServiceMemoWork. I only want to join to ServiceMemoWork if NONE of the values on the ServiceMemoWork table are equal to "No Service", for the given ServiceMemoID. Currently, if one of the rows is not equal to "No Service", the join occurs.

Any help would be greatly appreciated!

Roger_Ramjet

The easiest way to do that is with a sub-query, if your version supports them. If not then change version because they are just too usefull to do without.


$sql = "SELECT * FROM ServiceMemo s INNER JOIN ServiceMemoWork w ON (s.id=w.serviceMemoID) 
      WHERE s.id NOT IN (SELECT serviceMemoID FROM ServiceMemoWork WHERE reason = 'No Service')";

And I do wish people would stop naming columns 'id'. It is a bad habit that will trip you up often. Column names should be unique throughout a database - except for PK:FK pairs which should have exactly the same name.

frontporch

Unfortunately, my client's version of mySQL does not support subqueries. Is there a way to do this without subqueries??

Thanks!

Roger_Ramjet

Not this one as far as I can see. You'll have to run 2 queries and use a temp table to store the output from my subquery. You then Left Join to it and use IS NULL to elliminate all ids in the work table from your main selection.

frontporch

Roger, thanks for your quickly reply! Any chance you could provide an example of what you mean?

frontporch

I think I've got this working now. It turns out subqueries are supported in our version of mySQL (I just had the syntax wrong before). Thanks for your help, Roger!