Jump to content

Archived

This topic is now archived and is closed to further replies.

madfigs

Problem with long select statement

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.