• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

MySQL Cross Join Query

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
hdcowboyaz
Asked:
hdcowboyaz
  • 12
  • 3
  • 3
1 Solution
 
Dan CraciunIT ConsultantCommented:
Just a quick note: you're reusing the "m" alias. Try using unique aliases and see if it makes a difference.

HTH,
Dan
0
 
hdcowboyazAuthor Commented:
I changed it to 'k', same results
0
 
GanapathiFacets DeveloperCommented:
Remove the Group by clause and try it
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dan CraciunIT ConsultantCommented:
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
 
hdcowboyazAuthor Commented:
Removing the group by solved the duplicate row issue but now the last column is all wrong. See attached,
NoGroupBy.jpg
0
 
hdcowboyazAuthor Commented:
sql dump attached

Tables

ccode
sccode
job
materials
sctype
release
jds-s-0426.sql
0
 
hdcowboyazAuthor Commented:
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
 
GanapathiFacets DeveloperCommented:
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
 
hdcowboyazAuthor Commented:
See attached what it should return for dummy data
DummyWhatItShouldReturn.jpg
0
 
hdcowboyazAuthor Commented:
I tried re-writing like that. Was not successful.
0
 
GanapathiFacets DeveloperCommented:
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
 
hdcowboyazAuthor Commented:
Only return four columns and last row is still wrong.
Ganapathi.jpg
0
 
Dan CraciunIT ConsultantCommented:
It's Sunday and my brain is working slower.

Can you put in words what do you need your query to return?
0
 
hdcowboyazAuthor Commented:
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
 
hdcowboyazAuthor Commented:
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
 
hdcowboyazAuthor Commented:
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
 
hdcowboyazAuthor Commented:
Are you going to close this?
0
 
hdcowboyazAuthor Commented:
I just figured it out before I got help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now