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;
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;
Who is Participating?
Add Job_ID as a column in the second query and then use the job_ID from both queries in the ON clause.
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)/S
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'
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)/S
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'
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.
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.