Why doesn't this query work?

[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;

Is something obviously wrong with the statement? As far as I can tell it should work...

Thanks,
Scott C.

 

[tshot]

on the face of it yr quey seem 2 be correct
it seems that u have very lareg datat set

so in AND clause put the limiting value first i.e. whicjh returns fewre rows and in OR do the opposite, get most value variable first.




----------------
ur feedback is a very welcome desire

 

[r937]

it keeps on running because you are getting CROSS JOIN effects, it's trying to return a gazillion rows

that's because your tables are not joined properly, and in fact they probably cannot be

here's your query rewritten to be a little easier to understand:

[tt]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; [/tt]

see that OR there in red? that's the culprit

in any case, what you want is a UNION, not JOIN

if you're on mysql 4, just connect your two queries with UNION ALL, that'll bring out two rows, and then just add them up in your interface

if you're on 4.1, i think you can combine the two rows with a select from a derived table consisting of the result set from the union

otherwise, keep running two queries and then just add up the totals in your interface

rudy

http://r937.com/

 

[tshot]

Hi r937
yes u are right. i did not think of union here





----------------
ur feedback is a very welcome desire