Problem with long select statement

[madfigs]

Hi there,

 

I have two select statements that work independently, but when I try to combine them into one no results ever come up (it just keeps processing).

 

This is the composite statement:

SELECT Sum(labor.total), labor.service_code FROM labor, jobs, inventory WHERE ((labor.item = jobs.ideaxid AND jobs.date_picked_up > \'2003-03-01\') OR (labor.item = inventory.ideaxid AND inventory.date_created > \'2003-03-01\')) AND (labor.service_code = \'serv-2\' OR labor.service_code = \'serv-3\' OR labor.service_code = \'serv-12\' OR labor.service_code = \'serv-13\' OR labor.service_code = \'serv-21\') GROUP BY labor.service_code;

 

And these are the individual ones:

SELECT Sum(labor.total), labor.service_code FROM labor, jobs WHERE labor.item = jobs.ideaxid AND jobs.date_picked_up > \'2003-03-01\' AND (labor.service_code = \'serv-2\' OR labor.service_code = \'serv-3\' OR labor.service_code = \'serv-12\' OR labor.service_code = \'serv-13\' OR labor.service_code = \'serv-21\') GROUP BY labor.service_code;

 

SELECT Sum(labor.total), labor.service_code FROM labor, inventory WHERE labor.item = inventory.ideaxid AND inventory.date_created > \'2003-03-01\' AND (labor.service_code = \'serv-2\' OR labor.service_code = \'serv-3\' OR labor.service_code = \'serv-12\' OR labor.service_code = \'serv-13\' OR labor.service_code = \'serv-21\') GROUP BY labor.service_code;

 

My knowledge of MySQL is pretty basic, can anyone tell me if there is something obviously wrong with the statement? As far as I can tell it should work...

 

Thanks,

Scott C.

[Barand]

I\'ve had a go at cleaning it up for you.

 

Use INNER JOIN syntax. Not only is it more efficient but separating

the JOIN criteria from the WHERE makes it easier to understand.

 

SELECT Sum(labor.total) as laborTotal, labor.service_code

FROM (labor INNER JOIN jobs ON  labor.item = jobs.ideaxid)

INNER JOIN inventory ON labor.item = inventory.ideaxid

WHERE jobs.date_picked_up > \'2003-03-01\'

AND inventory.date_created > \'2003-03-01\'

AND labor.service_code IN (\'serv-2\', \'serv-3\',\'serv-12\', \'serv-13\',\'serv-21\')

GROUP BY labor.service_code;