当前位置: 动力学知识库 > 问答 > 编程问答 >

php - the whole data from tables is not coming via join query in mysql

问题描述:

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

where (DATE(registration.createddate)>='".$term1."'

AND DATE(registration.createddate)<='".$term2."')";

网友答案:

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

will.

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.

网友答案:

This :

$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

分享给朋友:
您可能感兴趣的文章:
随机阅读: