Solved

SQL Syntax

Posted on 2013-10-30
4
340 Views
Last Modified: 2013-10-30
This query works great but is limited to one job (i.e., JOB_ID = 7398).  I wish to open up the query so the first column JOB_ID displays all jobs.  However, if I remove WHERE j.job_id = 7398 in query a & b I get a cartesian product.

SELECT a.job_id,
       a.sctype,
       a.esthours,
       b.poest,
       (a.esthours * b.poest) AS total
  FROM (  SELECT j.job_id, s.sctype, SUM(c.popu) / (SUM(c.popu) / SUM(c.popu / c.pouest)) AS esthours
            FROM ccode c
                 INNER JOIN sccode s ON s.sccode_id = c.sccode_id
                 INNER JOIN job j ON c.job_id = j.job_id
           WHERE j.job_id = 7398
             AND s.sctype IN (1,
                              2,
                              3,
                              4,
                              5,
                              6,
                              7)
             AND c.popu > 0
             AND (j.deleted != 'Y' OR j.deleted IS NULL)
             AND (c.deleted != 'Y' OR c.deleted IS NULL)
             AND (s.deleted != 'Y' OR s.deleted IS NULL)
        GROUP BY j.job_id, s.sctype) a
       INNER JOIN (SELECT x.sctype, x.poest
                     FROM (  SELECT s.sctype, MAX(c.popu) popu, c.poest
                               FROM ccode c
                                    JOIN sccode s ON s.sccode_id = c.sccode_id
                                    JOIN job j ON j.job_id = c.job_id
                              WHERE j.job_id = 7398
                                AND s.sctype IN (1,
                                                 2,
                                                 3,
                                                 4,
                                                 5,
                                                 6,
                                                 7)
                                AND c.popu > 0
                           GROUP BY j.job_id, s.sctype) x) b
           ON a.sctype = b.sctype;
0
Comment
Question by:hdcowboyaz
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39612623
Add Job_ID as a column in the second query and then use the job_ID from both queries in the ON clause.
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39612636
To put a better way, change:

        GROUP BY j.job_id, s.sctype) a
       INNER JOIN (SELECT x.sctype, x.poest
                   FROM (  SELECT s.sctype, MAX(c.popu) popu, c.poest
To:

        GROUP BY j.job_id, s.sctype) a
       INNER JOIN (SELECT x.sctype, x.poest, j.job_ID
                   FROM (  SELECT s.sctype, MAX(c.popu) popu, c.poest, j.job_ID
and modify the last row from:

ON a.sctype = b.sctype;

To:

ON a.sctype = b.sctype  AND a.job_ID = b.job_ID.
0
 

Author Comment

by:hdcowboyaz
ID: 39612911
I used this..

SELECT
  a.job_id 'JOB ID',
  a.sctype 'TYPE',
  a.units 'UNITS',  
  FORMAT(a.esthours, 2) 'EstHours',
  b.poest 'RATE',
  FORMAT((a.esthours * b.poest), 2) 'EstCost',
  FORMAT((a.esthours * b.poest) / a.units, 4) 'UnitCost'
FROM
(SELECT
  j.JOB_ID,
  s.SCTYPE,
  SUM(c.POPU) AS UNITS,
  SUM(c.POPU)/(SUM(c.POPU)/SUM(c.POPU/c.POUEST)) AS EstHours
FROM ccode c
INNER JOIN sccode s ON s.sccode_id = c.sccode_id
INNER JOIN job j ON c.job_id = j.job_id
WHERE s.sctype IN (1,2,3,4,5,6,7)
AND c.popu > 0
AND (j.deleted != 'Y' OR j.deleted IS NULL)
AND (c.deleted != 'Y' OR c.deleted IS NULL)
AND (s.deleted != 'Y' OR s.deleted IS NULL)
GROUP BY j.job_id, s.sctype) a
INNER JOIN (SELECT x.sctype, x.poest, j.job_ID
FROM (SELECT s.sctype, MAX(c.popu) popu, c.poest, j.job_ID
FROM ccode c
JOIN sccode s ON s.sccode_id = c.sccode_id
JOIN job j ON j.job_id = c.job_id
WHERE s.sctype IN (1,2,3,4,5,6,7)
AND c.popu > 0
GROUP BY j.job_id, s.sctype) X) b
ON a.sctype = b.sctype  AND a.job_ID = b.job_ID;

I got this error...
Query : SELECT    a.job_id 'JOB ID',    a.sctype 'TYPE',   a.units 'UNITS',     format(a.esthours, 2) 'EstHours',    b.poest 'RATE',   f...
Error Code : 1054
Unknown column 'j.job_ID' in 'field list'
0
 

Author Closing Comment

by:hdcowboyaz
ID: 39613053
Only one small error

>    GROUP BY j.job_id, s.sctype) a
>    INNER JOIN (SELECT X.sctype, X.poest, X.job_ID
>    FROM (  SELECT s.sctype, MAX(c.popu) popu, c.poest, j.job_ID


There, in the 2nd line, X.job_ID
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now