?
Solved

MySQL Subquery

Posted on 2014-04-28
7
Medium Priority
?
336 Views
Last Modified: 2014-04-28
I need to add a column to the below table;

SELECT
CONCAT(s.SCCODE," ",s.SCC_DESC) 'Desc',
t.UM 'UoM',
c.POPU 'Orig Qty',
IF(t.UM = 'LS', 1, ROUND(c.POUEST, 0)) 'Orig PR',
FORMAT(((c.POPU / c.POUEST) * c.POEST), 0) 'Est Cost',
(SELECT FORMAT(SUM(l.HOURS), 0)
   FROM labor l
   INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   INNER JOIN sccode s2 ON s2.SCCODE_ID = c.SCCODE_ID
   INNER JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7398
   AND l.DATE_WORK <= '2014-04-20'
   AND l.DELETED = 'N'
   AND s2.SCTYPE = s.SCTYPE
   AND SUBSTRING(s2.SCCODE,1,5) = SUBSTRING(s.SCCODE,1,5)
) AS 'Actual PR'
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)
ORDER BY s.SCTYPE, s.SCCODE;

The column I need to add is:

 SELECT SUM(m.PLACEMENT)  
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7398
   AND m.DATE_PLACE <= '2014-04-20'
   AND m.DELETED = 'N'
   AND c.POPU > 0
   GROUP BY SUBSTRING(s.SCCODE,1,5)
   ORDER BY s.SCTYPE, s.SCCODE
 ;

I tried to add it as a subquery, like the one already in the main query, but can't get it to work.

I've attached the tables as an sql dump.
jds-s-0427.sql
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
  • 4
  • 2
7 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027853
what error are you getting when you run this....

SELECT
CONCAT(s.SCCODE," ",s.SCC_DESC) 'Desc',
t.UM 'UoM',
c.POPU 'Orig Qty',
IF(t.UM = 'LS', 1, ROUND(c.POUEST, 0)) 'Orig PR',
FORMAT(((c.POPU / c.POUEST) * c.POEST), 0) 'Est Cost',
(SELECT FORMAT(SUM(l.HOURS), 0)
   FROM labor l
   INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   INNER JOIN sccode s2 ON s2.SCCODE_ID = c.SCCODE_ID
   INNER JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7398
   AND l.DATE_WORK <= '2014-04-20'
   AND l.DELETED = 'N'
   AND s2.SCTYPE = s.SCTYPE
   AND SUBSTRING(s2.SCCODE,1,5) = SUBSTRING(s.SCCODE,1,5)
) AS 'Actual PR',
(SELECT SUM(m.PLACEMENT)  
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7398
   AND m.DATE_PLACE <= '2014-04-20'
   AND m.DELETED = 'N'
   AND c.POPU > 0
   GROUP BY SUBSTRING(s.SCCODE,1,5)
   ORDER BY s.SCTYPE, s.SCCODE) as 'ExtraColumn'

FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)
ORDER BY s.SCTYPE, s.SCCODE
0
 

Author Comment

by:hdcowboyaz
ID: 40027897
Error Code: 1242
Subquery returns more than 1 row
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 40028251
try this.
SELECT
CONCAT(s.SCCODE," ",s.SCC_DESC) 'Desc',
t.UM 'UoM',
c.POPU 'Orig Qty',
IF(t.UM = 'LS', 1, ROUND(c.POUEST, 0)) 'Orig PR',
FORMAT(((c.POPU / c.POUEST) * c.POEST), 0) 'Est Cost',
(SELECT FORMAT(SUM(l.HOURS), 0) 
   FROM labor l
   INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   INNER JOIN sccode s2 ON s2.SCCODE_ID = c.SCCODE_ID
   INNER JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7398
   AND l.DATE_WORK <= '2014-04-20'
   AND l.DELETED = 'N'
   AND s2.SCTYPE = s.SCTYPE
   AND SUBSTRING(s2.SCCODE,1,5) = SUBSTRING(s.SCCODE,1,5)
) AS 'Actual PR',
PLACEMENT
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN (SELECT SUBSTRING(s.SCCODE,1,5) SCCODE,SUM(m.PLACEMENT)  PLACEMENT 
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7398
   AND m.DATE_PLACE <= '2014-04-20'
   AND m.DELETED = 'N'
   AND c.POPU > 0
   GROUP BY SUBSTRING(s.SCCODE,1,5)) sub_query ON SUBSTRING(s.SCCODE,1,5) =  sub_query.SCCODE
WHERE j.JOB_ID = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
GROUP BY SUBSTRING(s.SCCODE,1,5)
ORDER BY s.SCTYPE, s.SCCODE; 

Open in new window

0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

Author Closing Comment

by:hdcowboyaz
ID: 40028421
Great...thanks
0
 

Author Comment

by:hdcowboyaz
ID: 40028452
opps, a problem....

The query now only returns four rows of data, there were nine.
The rows of data that disappeared don't have any values in the field m.PLACEMENTS.
These rows should still be there but have no data in that column 'PLACEMENT' that was added,
0
 
LVL 41

Expert Comment

by:Sharath
ID: 40028462
Change INNER JOIN with LEFT JOIN
0
 

Author Comment

by:hdcowboyaz
ID: 40028467
Interesting. It worked. The other day I changed to a Left Join and it made no difference. I suppose it depends on the query and data...
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

800 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