Solved

MySQL Subquery

Posted on 2014-04-28
7
328 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

20 Experts available now in Live!

Get 1:1 Help Now