Accepted Solution with splitting the rows into 3 tables and joining them. Worked fine, Trick the second join to join with first table


  • 0
    B

    I used counter to join the three filtered tables. The second join is with the first table not the 'Asia" table. If Asia is blank and you have a value for america and Europe- the below code still works. Small trick..!!

    select america.name as America,asia.name as Asia,europe.name as Europe from
        (select @counter1:= @counter1+1 as id,name from student,(select @counter1 :=0) as temp where continent = 'America' order by name) as america left outer join
        (select @counter2:= @counter2+1 as id,name from student,(select @counter2 :=0) as temp where continent = 'Asia' order by name) as asia on
    america.id = asia.id
    left outer join
        (select @counter3:= @counter3+1 as id,name from student,(select @counter3 :=0) as temp where continent = 'Europe' order by name) as europe on (america.id = europe.id )
    

  • 0
    B

    @bitsromeo This solution will work only if there are enough values in America column. If America column has ZERO values and the rest of the tables have values then the join will fail. Might be we can use union all for that corner case.


  • 0
    B

    This might be the final solution. Added a NULL condition so that if there are no values for america, then we can get the values for the other continents(from other tables)

    select america.name as America,asia.name as Asia,europe.name as Europe from
        (select @counter1:= @counter1+1 as id,name from student,(select @counter1 :=0) as temp where continent = 'America' order by name) as america left outer join
        (select @counter2:= @counter2+1 as id,name from student,(select @counter2 :=0) as temp where continent = 'Asia' order by name) as asia on
    america.id = asia.id or america.id is NULL
    left outer join
        (select @counter3:= @counter3+1 as id,name from student,(select @counter3 :=0) as temp where continent = 'Europe' order by name) as europe on (america.id = europe.id or america.id is NULL)

Log in to reply
 

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