php - MySql Select Result Having Combined Multiple Queries From Both the Same Table and Others -

i have table ('names') includes data related other data in other tables relying on ids. example:

*names table

id |     name     | predecessor | successor | house | birthplace  ----------------------------------------------------------------- 10    bayezid ii         9           11         4         null 11    selim            10          12         4         5 12    suleiman           11          13         4         61 

*houses table

id |  house -------------- 4    house of osman 

*places table

id |  place -------------- 5    amasya 61   trabzon 

what i'm trying accomplish construct query results in returning whole information depending on id, like:

{"result":[{     "id":"11",     "name":"selim i",     "predecessor": "bayezid ii",     "successor": "suleiman",     "house":"house of osman",     "birthplace":"amasya" }]} 

so, name of house , birthplace brought other tables ('houses', 'places') whereas predecessor , successor same table. need constructing query. thank you.

just self-join couple times, once predecessor's row (aliased n0 below), , once more successor's (n2):

select n1.id, n1.name, n0.name predecessor, n2.name successor names n1 left join names n0 on n1.predecessor = n0.id left join names n2 on n1.successor = n2.id 

sql fiddle demo

joining house , birthplace left exercise reader.