Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL query

Posted on 2014-04-27
2
Medium Priority
?
478 Views
Last Modified: 2014-04-27
I need to add a column to this query...

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',
       FORMAT(SUM(m.PLACEMENT), 0) 'Placed JTD'
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;

The column I'm adding is the following query...

SELECT SUM(l.HOURS) Hours_JTD
FROM labor l
INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
INNER JOIN sccode s ON s.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'
GROUP BY SUBSTRING(s.SCCODE,1,5);

The output from this query is four rows:
640
182.5
1924.5
986

When I try a Cross Join as below the last column the first query (Placed JTD) radically changes and the last column incorrectly returns:
640
640
640
640

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',
       FORMAT(SUM(m.PLACEMENT), 0) 'Placed JTD',
       l.Hours_JTD
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
CROSS JOIN (                                        /* l */
            SELECT SUM(l.HOURS) Hours_JTD
            FROM labor l
            INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
            INNER JOIN sccode s ON s.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'
                  GROUP BY SUBSTRING(s.SCCODE,1,5)
           ) l
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've attached a mysql dump with data.
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
2 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 2000 total points
ID: 40026323
The additional query must be run once-per-row based on the results of the first query. If this actually makes sense, then it should be doable as a subquery like this I believe. Untested, but fingers crossed...

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',
       FORMAT(SUM(m.PLACEMENT), 0) 'Placed JTD',
(SELECT SUM(l.HOURS) Hours_JTD
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 Hours_JTD
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;

Open in new window


The subquery uses the alias s2 instead of s as we need to join to columns in s of the main query.

This probably isn't the most efficient form of the query, but hopefully it will achieve what you need. Otherwise, some other experts may have time to suggest some alternatives.
0
 

Author Closing Comment

by:hdcowboyaz
ID: 40026334
Perfect


Thanks
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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

688 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