set @a = 0; set @b = 0; set @c = 0; Select America.name as America, Asia.name as Asia, Europe.name as Europe from (select name, @a := @a + 1 as id from student where continent = 'America' order by name) as America Left Join (select name, @b := @b + 1 as id from student where continent = 'Asia' order by name) as Asia on America.id = Asia.id Left Join (select name, @c := @c + 1 as id from student where continent = 'Europe' order by name) as Europe on America.id = Europe.id
First, thank you for this. However, I'm afraid the real-world solution is more complex. This solution assumes that America has the max # of rows of the 3 joined columns, so left joins are safe. If Asia or Europe had more rows in test data, this query would fail to produce the desired output.
I'm fiddling with this now, but it seems you'd need to "work around" a FULL JOIN in MySQL.. something like
(( america LEFT JOIN asia UNION ALL america RIGHT JOIN asia ) america_asia LEFT JOIN Europe ) UNION ALL (america_asia RIGHT JOIN Europe)