CODEWITHSUNDEEP
mysqlselectgroup-byinner-joincase-when
KriKox
KriKox

Reputation: 43

MySQL multiple SELECT query with CASE WHEN, INNER JOIN and GROUP BY1

I try to build a multiple select query with inner join and group by option afterwards and I do not really understand why my query does not work (I'm not familiar with SQL and php, maybe that's why). I get the error message that my syntax is wrong.

I have multiple tables with the same structure and I want to have some of the cases in one query grouped by one common variable.

The query is like that:

SELECT
    (SELECT Var1, 
    MAX( CASE WHEN Var2 ='2015' AND Var3='A1_U18' THEN Var4 ELSE 0 END ) AS 'A1_U18_0',
    MAX( CASE WHEN Var2 ='2015' AND Var3='A31_U6' THEN Var4 ELSE 0 END ) AS 'A31_U6_0'
    FROM table_a) AS a,

    (SELECT Var1,
    MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U3' THEN Var4 ELSE 0 END ) AS 'B21_U3_0',
    MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U6' THEN Var4 ELSE 0 END ) AS 'B21_U6_0'
    FROM table_b) AS b,

    (SELECT Var1,
    MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U3' THEN Var4 ELSE 0 END ) AS 'C21_U3_0',
    MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U6' THEN Var4 ELSE 0 END ) AS 'C21_U6_0'
    FROM table_c) AS c

INNER JOIN a b
    ON a.Var1 = b.Var1
INNER JOIN a c
    ON a.Var1 = c.Var1

GROUP BY a.Var1

What am I doing wrong?

Upvotes: 0

Views: 275

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

  1. Your subquery cant return multiple fields
  2. You miss include FROM a
  3. To declare alias you need to use backquote '`' you are using single quote ' and that is for string.

I highly recomend you do a basic mySql tutorial, This one help a lot

But You probably need something like this.

SELECT a.*, 
       b.`B21_U3_0`, b.`B21_U6_0`,
       c.`C21_U3_0`, c.`C21_U6_0`

FROM 
    (SELECT Var1, 
            MAX( CASE WHEN Var2 ='2015' AND Var3='A1_U18' 
                      THEN Var4 ELSE 0 END ) AS `A1_U18_0`,
            MAX( CASE WHEN Var2 ='2015' AND Var3='A31_U6' 
                      THEN Var4 ELSE 0 END ) AS `A31_U6_0`
     FROM table_a
     GROUP BY Var1
    ) AS a
JOIN    
    (SELECT Var1,
            MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U3' 
                      THEN Var4 ELSE 0 END ) AS `B21_U3_0`,
            MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U6' 
                      THEN Var4 ELSE 0 END ) AS `B21_U6_0`
     FROM table_b
     GROUP BY Var1 
    ) AS b
  ON a.Var1 = b.Var1
JOIN   
    (SELECT Var1,
           MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U3' 
                     THEN Var4 ELSE 0 END ) AS `C21_U3_0`,
           MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U6' 
                     THEN Var4 ELSE 0 END ) AS `C21_U6_0`
     FROM table_c
     GROUP BY Var1
    ) AS c
ON a.Var1 = c.Var1

Upvotes: 1

Related Questions