Geek Answers Handbook

Join mysql on yourself with a group

Table

id | name | year

Data:

  1 | ham | 2006 2 | ham | 2007 3 | ham | 2008 4 | amm | 2007 5 | amm | 2008 6 | inn | 2009

Now I am trying to build sql, which gives me the following output (or similar)

  name | y1 | y2 | y3 | y4 ham | 2006 | 2007 | 2008 | null amm | null | 2007 | 2008 | null inn | null | null | null | 2009

When doing several (independently) left joins, I kind of get this, but only if this name is set to 2006. Is there a way to achieve this?

+4
source share
3 answers

You want it to turn out what is called a “rotary” one, and you need a conditional sum :

 select name, sum(year = 2006) as y1, sum(year = 2007) as y2, sum(year = 2008) as y3, sum(year = 2009) as y4 from mytable group by name;

No associations, no problems, no problems. And it will work very well.

This works because in mysql true is 1 and false is 0 , so summing the condition counts how many times this was true!

Note that this will give you zeros instead of null for years without data, which is probably better. If you really need zeros, use if(sum(year = 2006) = 0, null, sum(year = 2006)) as y1 etc. But I hope you don't need this.

+5
source

Here is one of the methods shown in your example ( sqlfiddle ):

 SELECT t1.name, MAX(y1.year) AS y1, MAX(y2.year) AS y2, MAX(y3.year) AS y3, MAX(y4.year) AS y4 FROM theTable t1 LEFT OUTER JOIN theTable y1 ON t1.name = y1.name AND y1.year = 2006 LEFT OUTER JOIN theTable y2 ON t1.name = y2.name AND y2.year = 2007 LEFT OUTER JOIN theTable y3 ON t1.name = y3.name AND y3.year = 2008 LEFT OUTER JOIN theTable y4 ON t1.name = y4.name AND y4.year = 2009 GROUP BY t1.name
+1
source

If you don't mind the structure of the result, you can use group_concat :

 select name , group_concat(ifnull(year ,'null') separator ', ') as years from table

which displays the result as:

 name | years ham | 2006 , 2007 , 2008 , null amm | null , 2007 , 2008 , null inn | null , null , null , 2009
0
source

More articles:


All Articles