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
Solved

MySQL Subquery

Posted on 2014-04-28
7
332 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 41
sql query to calculate avaerage 21 52
INDEX does not make a difference, why? 10 59
Very Large data in MYSQL 7 73
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

840 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