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
hdcowboyazAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
Without sample data it can be awkward as I can't fully visualize or test. Perhaps this will do the trick but I'm not sure of the sccode join from jcost:
SELECT
        j.JOB_ID
      , SUM(l.HOURS)  AS MAN_HOURS
      , SUM(o.sum_wjcost)  AS sum_wjcost
FROM labor l
        INNER JOIN job j    ON l.JOB_ID = j.JOB_ID
        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
        INNER JOIN (
                    SELECT JOB_ID, SCCODE, SUM(WJCOST) AS sum_wjcost
                    FROM jcost
                    WHERE WEDATE = '2013-10-13'
                    GROUP BY JOB_ID, SCCODE
                   ) o ON j.JOB_ID = o.JOB_ID
                      AND c.SCCODE = o.SCCODE  --<< NOT sure about this
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
;

Open in new window

If this doesn't work it may be easier to resort to a correlated subquery
SELECT
        SUM(l.HOURS) AS MAN_HOURS
      , (
         SELECT SUM(WJCOST)
         FROM jcost o
         WHERE o.JOB_ID = j.JOB_ID
                 AND o.SCCODE = s.SCCODE
                 AND WEDATE = '2013-10-13
         ) AS SUM_WJCOST
FROM labor l
        INNER JOIN job j ON j.JOB_ID = l.JOB_ID
        INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
        INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
        INNER 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;
;

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
When you run just those two queries, how many rows are returned for each?
0
 
hdcowboyazAuthor Commented:
One
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PortletPaulfreelancerCommented:
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:
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
;

Open in new window

0
 
hdcowboyazAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
hdcowboyazAuthor Commented:
Thanks, The second solution worked.
0
 
PortletPaulfreelancerCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.