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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
When you run just those two queries, how many rows are returned for each?
hdcowboyazAuthor Commented:
One
PortletPaulEE Topic AdvisorCommented:
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

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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
Kent OlsenDBACommented:
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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hdcowboyazAuthor Commented:
Thanks, The second solution worked.
PortletPaulEE Topic AdvisorCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.