Jump to content


Photo

Problem with long select statement


  • Please log in to reply
1 reply to this topic

#1 madfigs

madfigs
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationRiverside, CA

Posted 28 October 2003 - 07:47 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 29 October 2003 - 08:15 PM

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_codeFROM (labor INNER JOIN jobs ON  labor.item = jobs.ideaxid)INNER JOIN inventory ON labor.item = inventory.ideaxidWHERE 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;

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users