Solved

SQL Syntax

Posted on 2013-10-30
4
345 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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