Jump to content

Problem with long select statement


madfigs

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.

Link to comment
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;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.