Solved

MySQL Cross Join Query

Posted on 2014-04-26
18
488 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

809 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