Solved

MySQL Cross Join Query

Posted on 2014-04-26
18
493 Views
Last Modified: 2014-05-02
This query returns two rows for each items when it should return one. See attachements for results.

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(m.Placed_JTD, 0) 'Placed JTD'
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
CROSS JOIN (
            SELECT SUM(m.PLACEMENT) Placed_JTD
            FROM materials m
            INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
            INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
            INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
            INNER JOIN job j ON m.JOB_ID = j.JOB_ID
                  WHERE m.JOB_ID = 7390
                  AND m.DATE_PLACE <= '2014-04-20'
                  AND c.TM = 'N'
                  AND m.DELETED = 'N'
                  GROUP BY SUBSTRING(s.SCCODE,1,5)
           ) m
WHERE j.JOB_ID = 7390
AND c.POPU > 0
ORDER BY s.SCTYPE, s.SCCODE;
WhatItReturns.jpg
WhatItShouldReturn.jpg
0
Comment
Question by:hdcowboyaz
  • 12
  • 3
  • 3
18 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40025399
Just a quick note: you're reusing the "m" alias. Try using unique aliases and see if it makes a difference.

HTH,
Dan
0
 

Author Comment

by:hdcowboyaz
ID: 40025401
I changed it to 'k', same results
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40025404
Remove the Group by clause and try it
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.

 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40025405
OK, then can we get the CREATE TABLE statements and some dummy data?

The CROSS JOIN is a Cartesian product so that's probably why you get 2 rows, but I have to test to be sure.
0
 

Author Comment

by:hdcowboyaz
ID: 40025408
Removing the group by solved the duplicate row issue but now the last column is all wrong. See attached,
NoGroupBy.jpg
0
 

Author Comment

by:hdcowboyaz
ID: 40025416
sql dump attached

Tables

ccode
sccode
job
materials
sctype
release
jds-s-0426.sql
0
 

Author Comment

by:hdcowboyaz
ID: 40025423
Use this code for the data. I took out "AND c.TM = 'N'" as its not necessary on not on the ccode table. Also, I changed it to look at JOB_ID = 7398.

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(k.Placed_JTD, 0) 'Placed JTD'
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
CROSS JOIN (
            SELECT SUM(m.PLACEMENT) Placed_JTD
            FROM materials m
            INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
            INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
            INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
            INNER JOIN job j ON m.JOB_ID = j.JOB_ID
                  WHERE m.JOB_ID = 7398
                  AND m.DATE_PLACE <= '2014-04-20'
                  AND m.DELETED = 'N'
           ) k
WHERE j.JOB_ID = 7398
AND c.POPU > 0
ORDER BY s.SCTYPE, s.SCCODE;
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40025425
Why cant you rewrite the query. Remove the CROSS JOIN and have the materials, ccode and release tables in primary INNER itself.

Sum it in the SELECT clause and include a Group By before the ORDER BY.
0
 

Author Comment

by:hdcowboyaz
ID: 40025426
See attached what it should return for dummy data
DummyWhatItShouldReturn.jpg
0
 

Author Comment

by:hdcowboyaz
ID: 40025428
I tried re-writing like that. Was not successful.
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40025451
I just rewrote the query but dint compile it. Try this out.

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 ccode c
INNER JOIN job       j   ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode    s   ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype    t   ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN release   r   ON r.CCODE_ID = c.CCODE_ID
INNER JOIN materials m ON m.JOB_ID = j.JOB_ID
WHERE
   j.JOB_ID = 7390
AND m.DATE_PLACE <= '2014-04-20'
AND m.DELETED = 'N'
AND c.TM = 'N'
AND c.POPU > 0
GROUP BY
   SUBSTRING(s.SCCODE,1,5)
ORDER BY
   s.SCTYPE, s.SCCODE;

Open in new window

0
 

Author Comment

by:hdcowboyaz
ID: 40025484
Only return four columns and last row is still wrong.
Ganapathi.jpg
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40025485
It's Sunday and my brain is working slower.

Can you put in words what do you need your query to return?
0
 

Author Comment

by:hdcowboyaz
ID: 40025494
The Placed JTD column is a sum of those records (m.PLACEMENTS) that are grouped by the categories on the left

10020 FOOTINGS
18310 SITEWORK
20010 TILT UP PANELS

I've included the tables as well as the expected results.
0
 

Author Comment

by:hdcowboyaz
ID: 40026147
This query returns the correct values for the first five columns but I need the last column added.

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',
       'Placed JTD'
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
WHERE j.JOB_ID = 7398
AND c.POPU > 0
ORDER BY s.SCTYPE, s.SCCODE;

This query returns the right values for the last column 'Placed JTD' but when I do and insert it doesn't work because it returns four values, not one. When I try the Cross Join the numbers are wrong.

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

Accepted Solution

by:
hdcowboyaz earned 0 total points
ID: 40026156
This is the answer....

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
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
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 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;
0
 

Author Comment

by:hdcowboyaz
ID: 40036060
Are you going to close this?
0
 

Author Closing Comment

by:hdcowboyaz
ID: 40036643
I just figured it out before I got help.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

790 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