CODEWITHSUNDEEP
phpmysqlsql
Mooelb
Mooelb

Reputation: 31

Combine queries.. PIVOT doesn't work

I have a problem with my queries. I have now 2 queries and that works fine. But when I want to add another query with a PIVOT, it doesn't work. I have tried a lot of things but nothing works..

This is my first two queries

$query = "SET SQL_BIG_SELECTS = 1;";
$query .= "SELECT * FROM datakram, datakram2, datakram3 WHERE datakram.NAME = datakram2.NAME AND datakram2.NAME = datakram3.NAME"

And I want to add a PIVOT for table "datakram4". But I want only the rows where the NAME is equal to the NAME in the others tables. Without the PIVOT it works..

My PIVOT code.

SELECT  `name` ,
 MAX( CASE WHEN  `year` =2017 THEN  `income` ELSE 0 END ) AS INCOME_2017,
 MAX( CASE WHEN  `year` =2017 THEN  `expense` ELSE 0 END ) AS EXPENSE_2017,
 MAX( CASE WHEN  `year` =2016 THEN  `income` ELSE 0 END ) AS INCOME_2016,
 MAX( CASE WHEN  `year` =2016  THEN  `expense` ELSE 0 END ) AS EXPENSE_2016
 FROM  `test_data` GROUP BY  `name`

I use multi_query for my php script.

Upvotes: 0

Views: 27

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

A simple way is to filter in the WHERE clause:

SELECT  `name` ,
         MAX(CASE WHEN `year` = 2017 THEN `income` ELSE 0 END) AS INCOME_2017,
         MAX(CASE WHEN `year` = 2017 THEN `expense` ELSE 0 END) AS EXPENSE_2017,
         MAX(CASE WHEN `year` = 2016 THEN `income` ELSE 0 END) AS INCOME_2016,
         MAX(CASE WHEN `year` = 2016  THEN `expense` ELSE 0 END) AS EXPENSE_2016
FROM `test_data` td
WHERE EXISTS (SELECT 1 FROM FROM datakram d WHERE d.name = td.NAME) AND
      EXISTS (SELECT 1 FROM FROM datakram2 d WHERE d.name = td.NAME) AND
      EXISTS (SELECT 1 FROM FROM datakram3 d WHERE d.name = td.NAME) 
GROUP BY `name` ;

Upvotes: 1

Parfait
Parfait

Reputation: 107727

Simply join the queries:

SELECT d4.* 
FROM datakram d1
INNER JOIN datakram2 d2
  ON d1.`NAME` = d2.`NAME`
INNER JOIN datakram3 d3 
  ON d2.`NAME` = d3.`NAME`
INNER JOIN
   (SELECT `name` ,
      MAX(CASE WHEN `year`=2017 THEN `income` ELSE 0 END) AS INCOME_2017,
      MAX(CASE WHEN `year`=2017 THEN `expense` ELSE 0 END) AS EXPENSE_2017,
      MAX(CASE WHEN `year`=2016 THEN `income` ELSE 0 END) AS INCOME_2016,
      MAX(CASE WHEN `year`=2016 THEN `expense` ELSE 0 END) AS EXPENSE_2016
    FROM `test_data` 
    GROUP BY `name`
   ) d4
  ON d3.`NAME` = d4.`name`

Upvotes: 2

Related Questions