SQL Syntax
This query is correct...
SELECT SUM(l.HOURS) AS `MAN HOUR`
FROM labor l
LEFT JOIN job j ON j.JOB_ID = l.JOB_ID
LEFT JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON t.SCTYPE_ID =s.SCTYPE
WHERE j.JOB_ID = 7398
AND l.DATE_WORK BETWEEN '2013-10-07' AND '2013-10-13'
AND t.SCTYPE_ID IN (1,7)
GROUP BY j.JOB_ID;
This query is correct...
SELECT o.WJCOST
FROM jcost o
LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
WHERE j.JOB_ID = 7398
AND o.WEDATE = '2013-10-13';
When I attempt to add the column from the second query o.WJCOST to the first query I get a cartesian product.
I've attached the ERD.
ERD-Q2.jpg
SELECT SUM(l.HOURS) AS `MAN HOUR`
FROM labor l
LEFT JOIN job j ON j.JOB_ID = l.JOB_ID
LEFT JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON t.SCTYPE_ID =s.SCTYPE
WHERE j.JOB_ID = 7398
AND l.DATE_WORK BETWEEN '2013-10-07' AND '2013-10-13'
AND t.SCTYPE_ID IN (1,7)
GROUP BY j.JOB_ID;
This query is correct...
SELECT o.WJCOST
FROM jcost o
LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
WHERE j.JOB_ID = 7398
AND o.WEDATE = '2013-10-13';
When I attempt to add the column from the second query o.WJCOST to the first query I get a cartesian product.
I've attached the ERD.
ERD-Q2.jpg
Kent Olsen
When you run just those two queries, how many rows are returned for each?
ASKER
One
If both queries supply one result record, then the Cartesian product is: 1 * 1 = 1
But as you didn't provide the query when you tried to combine them, or sample data, it's not possible to verify what that unwanted result was doing. However I can point out that there is no point in using left joins and then including selection criteria against those joined tables in the where clause that prohibit a null. e.g.
FROM jcost o LEFT JOIN job j ON j.JOB_ID = o.JOB_ID WHERE j.JOB_ID = 7398
exactly the same data would be returned by this:
FROM jcost o INNER JOIN job j ON j.JOB_ID = o.JOB_ID WHERE j.JOB_ID = 7398
Could you try this as the combined query please:
But as you didn't provide the query when you tried to combine them, or sample data, it's not possible to verify what that unwanted result was doing. However I can point out that there is no point in using left joins and then including selection criteria against those joined tables in the where clause that prohibit a null. e.g.
FROM jcost o LEFT JOIN job j ON j.JOB_ID = o.JOB_ID WHERE j.JOB_ID = 7398
exactly the same data would be returned by this:
FROM jcost o INNER JOIN job j ON j.JOB_ID = o.JOB_ID WHERE j.JOB_ID = 7398
Could you try this as the combined query please:
SELECT
j.JOB_ID
, SUM(l.HOURS) AS MAN_HOURS
, SUM(o.WJCOST) AS sum_wjcost
FROM labor l
INNER JOIN job j ON l.JOB_ID = j.JOB_ID
INNER JOIN jcost o ON j.JOB_ID = o.JOB_ID AND o.WEDATE = '2013-10-13'
INNER JOIN ccode c ON l.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
WHERE j.JOB_ID = 7398
AND l.DATE_WORK BETWEEN '2013-10-07' AND '2013-10-13'
AND t.SCTYPE_ID IN (1, 7)
GROUP BY j.JOB_ID
;ASKER
I'm sorry. There is one row returned by the first query SUM(l.HOURS) = 36.00
There are two rows returned by the second query
o.WJCOST = 842.32
o.WJCOST = 645.13
I only want the first row for o.WJCOST which will be selected via the same criteria t.SCTYPE_ID IN (1, 7)
o.WJCOST = 842.32 has a t.SCTYPE_ID = 1
o.WJCOST = 645.13 has a t.SCTYPE_ID = 5
When I ran your query I got...
JOB_ID MAN_HOURS sum_wjcost
7398 72.00 7437.25
36 X 2 = 72 that makes sence because of the two rows
but
(842.32 + 645.13) x 5 = 7437.25 ???
The correct result should be:
JOB_ID MAN_HOURS sum_wjcost
7398 36.00 842.32
There are two rows returned by the second query
o.WJCOST = 842.32
o.WJCOST = 645.13
I only want the first row for o.WJCOST which will be selected via the same criteria t.SCTYPE_ID IN (1, 7)
o.WJCOST = 842.32 has a t.SCTYPE_ID = 1
o.WJCOST = 645.13 has a t.SCTYPE_ID = 5
When I ran your query I got...
JOB_ID MAN_HOURS sum_wjcost
7398 72.00 7437.25
36 X 2 = 72 that makes sence because of the two rows
but
(842.32 + 645.13) x 5 = 7437.25 ???
The correct result should be:
JOB_ID MAN_HOURS sum_wjcost
7398 36.00 842.32
Use one of the aggregate functions in the second query to get the min(), max(), or other desired value.
The critical thing is to get one results from each sub-query.
Kent
The critical thing is to get one results from each sub-query.
Kent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, The second solution worked.
Just as a warning, use of correlated subqueries within the select clause are not the most efficient way of gathering information; you won't notice it in this query because you are filtering for a very small set of information; but it could become noticeable - as an example - if you wanted these numbers for each job.