Solved

MySQL Cross Join Query

Posted on 2014-04-26
18
473 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

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 …
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 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

21 Experts available now in Live!

Get 1:1 Help Now