Solved

MySQL Subquery

Posted on 2014-04-28
7
331 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
  • 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 40

Accepted Solution

by:
Sharath earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 40

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now