CODEWITHSUNDEEP
mysqlsubquery
DDay
DDay

Reputation: 100

mysql union / mix two tables

i have a query which is below:

SELECT ROUND(SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.achievedScore ELSE 0 END)/SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.applicableScore ELSE 0 END)*100) AS score1,
    scoreanalysis.sectionName, 
    scoreanalysis.sectionID, 
    scoreanalysis.wave, 
    scoreanalysis.waveID

    FROM shopcategories
    INNER JOIN locationcategories ON shopcategories.ID = locationcategories.shopcategoryID                          
    INNER JOIN scoreanalysis ON locationcategories.territoryID = scoreanalysis.territoryID                      
    WHERE shopcategories.formatID = 52 AND locationcategories.isClient =  0 
    and scoreanalysis.waveID = 160
    GROUP BY scoreanalysis.sectionID 
    ORDER BY scoreanalysis.sectionID

this outputs as

enter image description here

and i have other query which is like same like above but with bit of difference.

SELECT ROUND(SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.achievedScore ELSE 0 END)/SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.applicableScore ELSE 0 END)*100) AS score2
FROM shopcategories
INNER JOIN locationcategories ON shopcategories.ID = locationcategories.shopcategoryID                          
INNER JOIN scoreanalysis ON locationcategories.territoryID = scoreanalysis.territoryID                      
WHERE shopcategories.formatID = 52 AND locationcategories.isClient =  1 
and scoreanalysis.waveID = 160
GROUP BY scoreanalysis.sectionID 
ORDER BY scoreanalysis.sectionID

enter image description here

what i want is to mix tables to display as below:

enter image description here

any possible solution to this? Any help thanks

Upvotes: 0

Views: 79

Answers (2)

JimmyB
JimmyB

Reputation: 12610

You can join/reference the same table more than once in a single query, like:

SELECT
scoreanalysis.sectionID, 
ROUND(SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.achievedScore ELSE 0 END)/SUM(CASE WHEN scoreanalysis.waveID = 160 THEN scoreanalysis.applicableScore ELSE 0 END)*100) AS score1,

ROUND(SUM(CASE WHEN score2.waveID = 160 THEN score2.achievedScore ELSE 0 END)/SUM(CASE WHEN score2.waveID = 160 THEN score2.applicableScore ELSE 0 END)*100) AS score2

FROM shopcategories
INNER JOIN locationcategories ON shopcategories.ID = locationcategories.shopcategoryID                          
INNER JOIN scoreanalysis ON locationcategories.territoryID = scoreanalysis.territoryID

-- Join a second time:
INNER JOIN locationcategories AS location2 ON shopcategories.ID = location2.shopcategoryID                          
INNER JOIN scoreanalysis AS score2 ON scoreanalysis.sectionID = score2.sectionID AND location2.territoryID = score2.territoryID           

WHERE shopcategories.formatID = 52
AND locationcategories.isClient =  0 
AND location2.isClient = 1 -- Reference to alias
AND scoreanalysis.waveID = 160
AND score2.waveID = 160 -- Reference to alias
GROUP BY scoreanalysis.sectionID 
ORDER BY scoreanalysis.sectionID

Upvotes: 1

Genislav Nastev
Genislav Nastev

Reputation: 1

In order to display the content into one table you need to join them, and in order to do so, the two tables has to have similar parameters in between.

Upvotes: 0

Related Questions