how to put alias inside join statement

hi I want to put an alias in my join statement. my code is: SELECT distinct id_no, '' as picture, concat(lastname, IF(LENGTH(firstname) > 0 OR LENGTH(middlename) > 0, ', ', ''), concat(IF(p.degree = 5, 'Dr. ', IF(p.degree = 6, 'Dr. ', IF(p.degree = 28, 'Dr. ', '')))), firstname, IF(LENGTH(middlename) > 0, CONCAT(' ', middlename), '')) as name, tp.profession, '' as cv, e.entry_date, e.termination_date FROM (bims_people as p) LEFT JOIN bims_people_education as bpe ON bpe.people_id = p.id_no LEFT JOIN tcms_profession as tp ON tp.profession_id = bpe.profession LEFT JOIN (select * from bims_people_employment_contract_data order by termination_date DESC) e ON e.people_id = p.id_no WHERE p.marked = 0 AND p.id_no > 1 GROUP BY p.id_no ORDER BY lastname ASC LIMIT 100, 50 ; and I want it this way: SELECT distinct id_no, '' as picture, concat(lastname, IF(LENGTH(firstname) > 0 OR LENGTH(middlename) > 0, ', ', ''), concat(IF(p.degree = 5, 'Dr. ', IF(p.degree = 6, 'Dr. ', IF(p.degree = 28, 'Dr. ', '')))), firstname, IF(LENGTH(middlename) > 0, CONCAT(' ', middlename), '')) as name, tp.profession, '' as cv, e.entry_date, e.termination_date FROM (bims_people as p) LEFT JOIN bims_people_education as bpe ON bpe.people_id = p.id_no LEFT JOIN tcms_profession as tp ON tp.profession_id = bpe.profession LEFT JOIN (select * from bims_people_employment_contract_data **where people_id = p.id_no** order by termination_date DESC limit 1) e ON e.people_id = p.id_no WHERE p.marked = 0 AND p.id_no > 1 GROUP BY p.id_no ORDER BY lastname ASC LIMIT 100, 50 ; i keeps telling me that p.id_no is unknown;