sql - Mysql - Joining one to many to many -

i wrote following code in order join 1 many many in sql server 2008 r2. code partitions select statement id , orders can grab top 3 diagnosis:

   left join (select zzabstractlink,                  diagnosiscihivalue,                 diagnosistype,                 rownumber = row_number() over(partition zzabstractlink order diagnosisoccurrence)                 i10_diagnosis_vr                 diagnosistype <> 'm') dx1 on ab.abstractid = dx1.zzabstractlink , dx1.rownumber = 1        left join (select zzabstractlink,                  diagnosiscihivalue,                 diagnosistype,                 rownumber = row_number() over(partition zzabstractlink order diagnosisoccurrence)                 i10_diagnosis_vr                 diagnosistype <> 'm') dx2 on ab.abstractid = dx2.zzabstractlink , dx2.rownumber = 2     left join (select zzabstractlink,                  diagnosiscihivalue,                 diagnosistype,                 rownumber = row_number() over(partition zzabstractlink order diagnosisoccurrence)                 i10_diagnosis_vr                 diagnosistype <> 'm') dx3 on ab.abstractid = dx3.zzabstractlink , dx3.rownumber = 3 

i need write same code in mysql. know how achieve this?

here sample code:

create table person  (person varchar(10))  create table diagnosis ( person varchar(10), occurence varchar(10), diagnosis varchar(10))   insert person values (1),(2)  insert diagnosis values (1,2,a),(1,1,b),(1,3,c),(1,4,d),(2,2,a),(2,3,b),(2,1,c) 

select statment outcome:

person  |diagnosisoccurence1    |diagnosisoccurence2 _____________________________________________________ 1       |b                      |a 2       |c                      |a