SQL Syntax

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;
hdcowboyazAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
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
 
Shaun KlineLead Software EngineerCommented:
Add Job_ID as a column in the second query and then use the job_ID from both queries in the ON clause.
0
 
hdcowboyazAuthor Commented:
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
 
hdcowboyazAuthor Commented:
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
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.