pivot table - LEFT JOIN doesn't behave as expected as gives NULLs in MySQL -

i completing this challenge hackerrank.

it asks:

pivot occupation column in occupations each name sorted alphabetically , displayed underneath corresponding occupation. output column headers should doctor, professor, singer, , actor, respectively.

it involves table of data:

name       occupation      ashley      professor  samantha actor  julia         doctor  britney     professor  maria        professor  meera       professor  priya         doctor  priyanka    professor  jennifer     actor  ketty         actor  belvet professor  naomi professor  jane singer  jenny singer  kristeen singer  christeen singer  eve actor  aamina doctor 

we want pivot table occupation such each name sorted alphabetically , displayed underneath corresponding occupation. output column headers (which don't actually) should doctor, professor, singer, , actor, respectively.

however, when run mysql code:

select d.name, p.name, s.name, a.name  (     select @row_number:=@row_number+1 row_number, name     occupations, (select @row_number:=0) t     occupation = 'professor'     order name     ) p left join (     select @row_number:=@row_number+1 row_number, name     occupations, (select @row_number:=0) t     occupation = 'doctor'     order name     ) d on d.row_number =p.row_number left join (     select @row_number:=@row_number+1 row_number, name     occupations, (select @row_number:=0) t     occupation = 'singer'     order name     ) s on p.row_number =s.row_number left join (     select @row_number:=@row_number+1 row_number, name     occupations, (select @row_number:=0) t     occupation = 'actor'     order name     ) on p.row_number =a.row_number 

the left joins don't behave expected , get:

null ashley null null  null belvet null null  null britney null null  null maria null null  null meera null null  null naomi null null  null priyanka null null 

this doesn't make sense me -- why join produce many nulls? mysql behave in way such can't number multiple tables? not clear me.

i'm supposing reason isn't matching way think should @row_number isn't resetting 1 each subquery.

i tested out, joining first 2 (professors , doctors), using cross join, see row_number values.

+------------+--------+------------+----------+ | row_number | name   | row_number | name     | +------------+--------+------------+----------+ |          8 | aamina |          1 | ashley   | |          8 | aamina |          2 | belvet   | |          8 | aamina |          3 | britney  | |          8 | aamina |          4 | maria    | |          8 | aamina |          5 | meera    | |          8 | aamina |          6 | naomi    | |          8 | aamina |          7 | priyanka | |          9 | julia  |          1 | ashley   | |          9 | julia  |          2 | belvet   | |          9 | julia  |          3 | britney  | |          9 | julia  |          4 | maria    | |          9 | julia  |          5 | meera    | |          9 | julia  |          6 | naomi    | |          9 | julia  |          7 | priyanka | |         10 | priya  |          1 | ashley   | |         10 | priya  |          2 | belvet   | |         10 | priya  |          3 | britney  | |         10 | priya  |          4 | maria    | |         10 | priya  |          5 | meera    | |         10 | priya  |          6 | naomi    | |         10 | priya  |          7 | priyanka | +------------+--------+------------+----------+ 

you can see apparently row numbers incremented progressively, , initial value of 1 in both subqueries has been done time rows numbered.

you might able fix using distinct user variable in each subquery.

but query not work way want anyway, example if ever have fewer professors members of other professions.

honestly, not kind of columnar formatting in sql. 4 independent queries, fetch results application, , format columns output. simpler way, , simple code easier write, easier debug, easier maintain.

---

re comment:

fair enough, doing coding challenge fine, long (and other readers) know in real project, doing excessively clever sql isn't best idea.

since you're doing coding challenge, should solve yourself, can't give solution produces output below. evidence it's possible (i promise did not mock output, copy & pasted terminal window). luck!

+------------+-----------+--------+-----------+----------+ | row_number | professor | doctor | singer    | actor    | +------------+-----------+--------+-----------+----------+ |          1 | ashley    | aamina | christeen | eve      | |          2 | belvet    | julia  | jane      | jennifer | |          3 | britney   | priya  | jenny     | ketty    | |          4 | maria     | null   | kristeen  | samantha | |          5 | meera     | null   | null      | null     | |          6 | naomi     | null   | null      | null     | |          7 | priyanka  | null   | null      | null     | +------------+-----------+--------+-----------+----------+