Accept solution


  • 3
    X
    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
    
    

  • 1
    S

    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)

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.