i'm working with php mysql and there are 12 tables which contains student informations.There are 3 main table First table is registration, second is demandraft and third is creditcard.The demandraft table contains all the creditcard table fields but as empty. now i want to get the whole data from these three tables to generate my xls file but coz there are empty fields of creditcard table in demandraft table so unable to fetch the whole records from all 3 tables. there is stuid field common in all 3 tables.
Here is my join query for that:
$sql = "select * from registration
join programme on registration.id=programme.stuid
join family on registration.id=family.stuid
join address on registration.id=address.stuid
join education on registration.id=education.stuid
join extradetail on registration.id=extradetail.stuid
join workexperience on registration.id=workexperience.stuid
join demanddraft on registration.id=demanddraft.stuid
join payonline on registration.id=payonline.stuid
Use a left join.
select * from a join b on a.id = b.a_id
will not list lines of table a that do not appear in table b.
select * from a left join b on a.id = b.a_id
Left join might be a bit tricky when chaining many of them with multiple tables. You may have to cleverly use parentheses around joins such that the order of joins is correct.
$sql = "select * from registration left join programme on registration.id=programme.stuid left join family on registration.id=family.stuid left join address on registration.id=address.stuid left join education on registration.id=education.stuid left join extradetail on registration.id=extradetail.stuid left join workexperience on registration.id=workexperience.stuid left join demanddraft on registration.id=demanddraft.stuid left join payonline on registration.id=payonline.stuid where (DATE(registration.createddate)>='".$term1."' AND DATE(registration.createddate)<='".$term2."')";
Should do the trick