?
Solved

SQL Syntax

Posted on 2013-10-30
4
Medium Priority
?
346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 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