You must Sign In to post a response.
  • Category: SQL Server

    How to Join 7 tables with Empty Table (if whether Table Null or Not Null)

    hi Developers ,

    I want to 7 number of tables in my application and get all the records both Empty and Non Empty tables in a GridView.

    Four(4) number of tables only i having records in first time. till i have no records in remaining three tables
    then i am add records to 5th table (then 5th value is picked up and show automatically with last 4 tables)
    then i am add records to 6th table ( then 5th value is picked up and show automatically with last 5 tables)
    then i am add records to 7th table (then 5th value is picked up and show automatically with last 4 tables)

    but in First time when i am open the page it should be display all the tables and fields on 7 tables.
    if there is value available it should show the value otherwise it display Empty value.
    But the All Columns should be bind in the gridview. because i need to fetch all the tables and all the columns whether null or not null

    select * from tbl_orderreceived qt inner join tbl_despatched t6 on qt.enquiryid=t6.enquiryid
    inner join tbl_invoice t7 on t6.enquiryid=t7.enquiryid
    inner join tbl_supplier t8 on t7.enquiryid=t8.enquiryid
    inner join tbl_fr fr on fr.enquiryid=qt.enquiryid inner join
    tbl_enquiryregister er on er.enquiryid=fr.enquiryid inner join tbl_quotation t5 on
    t5.enquiryid=er.enquiryid where t5.status='A' order by t5.orderno asc

    Above is my query . in my query give correct output.but working only if all the 7tables having the same record na it gives the output. otherwise it gives Empty Result.
    Even if the 7th table only is Null it gives also Empty Result.

    so please suggest me how to i am get all the tables and all the columns whether it is Null or Not Null table.

    thanking you
    Paul.S
  • #768560
    Ofcourse,

    It will give you only all the columns are matching because of Inner Join. Only the matching row will be displayed.
    Instead of Inner join try out Left Outer Join or Right outer Join. like below,



    select * from tbl_orderreceived qt Left OUTER join tbl_despatched t6 on qt.enquiryid=t6.enquiryid
    Left OUTER join tbl_invoice t7 on t6.enquiryid=t7.enquiryid
    Left OUTER join tbl_supplier t8 on t7.enquiryid=t8.enquiryid
    inner join tbl_fr fr on fr.enquiryid=qt.enquiryid inner join
    tbl_enquiryregister er on er.enquiryid=fr.enquiryid inner join tbl_quotation t5 on
    t5.enquiryid=er.enquiryid where t5.status='A' order by t5.orderno asc



    So you have mentioned upto table 5 you have values and table6,7 and 8 their might be values of no values.

    So I have put the Left Outer Join for those 3 tables. Kindly check the query I have not executed the query it might have syntax error. But basic idea is instead of Inner join try out Left or Right outer join.

    Thanks,
    Mani

  • #768565
    Have you tried with ' UNION ALL' it helps you to combine all your records and bind them to gridview
    see below query

    SELECT [TEAM], [LEVEL], MAX(v1) Value1, MAX(v2) Value2, MAX(v3) Value3
    FROM (
    SELECT [TEAM], [LEVEL], v1, NULL v2, NULL v3
    FROM TABLE1
    UNION ALL
    SELECT [TEAM], [LEVEL], NULL v1, V2, NULL v3
    FROM TABLE2
    UNION ALL
    SELECT [TEAM], [LEVEL], NULL V1, NULL V2, V3
    FROM TABLE3
    ) t0
    GROUP BY [TEAM], [LEVEL]

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768597
    Hi,

    If you want to fetch nullable columns too, then inner join won't work you should use either left join or right join.

    Before taking a decision you should be very clear what to consider and when to consider.

    First Google it the purpose of Left and Right joins and then start with one small sample that will give clear picture about the concept then try to implement in your project.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments