mysql - Multiple SQL JOIN statement -

i have make query 2 table, example here table structure "student" table:

------------------------------------------- id  |  name  |  mom_job_id  |  dad_job_id ------------------------------------------- 1   | test1  | 1            |  2 2   | test2  | 3            |  1 ------------------------------------------- 

and have "job" table :

--------------------------- id  | job_name --------------------------- 1   | designer 2   | writer 3   | programmer --------------------------- 

i want select record on student table , replace "mom_job_id" , "dad_job_id" job_name on "job table"

i have tried query :

select student.id, job.job_name mom_job, job.job_name dad_job student inner join job on mom_job_id = job.id id=1 

that query work fine, dad's job set same mom's job, add inner join , make query :

select student.id, job.job_name mom_job, job.job_name dad_job student inner join job on mom_job_id = job.id inner join job on dad_job_id = job.id id=1 

the query work not returned record. how solve this? belive problem came job.id

you close one, give job table different alias each time join it.

select s.id, job_mom.job_name mom_job, job_dad.job_name dad_job student s inner join job job_mom on s.mom_job_id = job_mom.id inner join job job_dad on s.dad_job_id = job_dad.id s.id=1 

in event job table join not return row , still want show student table data, can use left join:

select s.id, job_mom.job_name mom_job, job_dad.job_name dad_job student s left join job job_mom on s.mom_job_id = job_mom.id left join job job_dad on s.dad_job_id = job_dad.id s.id=1