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

    Problem in Joining 4 Sql tables (Record display 3 times )

    hi Developers ,

    i need to joining 4 tables . i have done. but i can't able to get exact output.
    Each column of the table Displays 3 or 4 times

    My using Sql Query is Below

    select * from tbl_Quotation t1 inner join tblcostsheet_withoutcomponent
    t2 on t1.enquiryid=t2.enquiryid inner join tbl_fr t3 on t3.enquiryid=t2.enquiryid inner join
    tbl_enquiryregister t4 on t4.enquiryid=t3.enquiryid where t1.status='A' and
    t1.approval_status='Pending Approval' order by t1.quotation_id asc

    Please help me to am get exact output or give some other idea to i am done this task

    Thanks with
    Paul.S
  • #767524
    Hi
    Paul

    your query ok. can you share 4 table structure with sample data then only we can easily find out your issue and find your query quickly. so can you post your table structure and sample Data.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767526
    Yes Fine My table Structure Given Below.

    my first table name : TBL_QUOTATION

    [quotation_id] [bigint] IDENTITY(1,1) NOT NULL,
    [QuotationNumber] [varchar](max) NULL,
    [CostSheetNumber] [varchar](max) NULL,
    [enquiryid] [varchar](max) NULL,
    [fr_id] [varchar](max) NULL,
    [QuotationDate] [varchar](max) NULL,
    [QuotationValidity] [varchar](max) NULL,
    [QuoteReference] [varchar](max) NULL,
    [remarks] [varchar](max) NULL,
    )

    My Second Table name : TBL_COSTSHEET_WITHOUTCOMPONENT

    [costsheet_id] [bigint] IDENTITY(1,1) NOT NULL,
    [costsheetnumber] [varchar](max) NULL,
    [enquiryid] [varchar](max) NULL,
    [fr_id] [varchar](max) NULL,
    [ProductionFacility] [varchar](max) NULL,
    [QuoteReference] [varchar](max) NULL,
    [remarks] [varchar](max) NULL,

    My Third Table name : TBL_FR

    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [fr_id] [varchar](max) NULL,
    [enquiryid] [varchar](max) NULL,
    [boardname] [varchar](max) NULL,
    [boardshape] [varchar](max) NULL,

    My Fourth Table name : TBL_ENQUIRYREGISTER

    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [enquiryid] [varchar](max) NULL,
    [designation] [varchar](max) NULL,
    [enquirername] [varchar](max) NULL,
    [enquirer_billing_address] [varchar](max) NULL,
    [postalcode] [varchar](max) NULL,
    [countrycode] [varchar](max) NULL,
    [citycode] [varchar](max) NULL,
    [phoneno] [varchar](max) NULL,
    [countrycode2] [varchar](max) NULL,
    [citycode2] [varchar](max) NULL,
    [phoneno2] [varchar](max) NULL,
    [emailid] [varchar](max) NULL,
    [emailid2] [varchar](max) NULL,
    [remarks] [varchar](max) NULL,
    [lead_time_to_delivery] [varchar](max) NULL,

    in all the tables i have a unique id name called like ENQUIRY ID .

    Paul.S

  • #767528
    Hi,

    what it means "Each column of the table Displays 3 or 4 times"?

    I assume that each row repeats 3-4 times right? If your joining conditions are correct then it should give exact data.

    While making query using more than 1 table we should be very careful, try to join carefully and has to be checked each and every join vary careful, first join 2 tables and check the result if it is good then join 3rd table and compare the result like that you have to join tables, if any entry repeated more than once that means you missed some conditions while joining table, so make sure you have to use all the conditions properly.

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

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

  • #767529
    when i am using 3 three tables it working fine , exact mr.Naceen but if i am add fourth table then only problem is started. that is my problem

    three tables joining Code

    elect * from tblCostSheet_WithoutComponent cs inner join tbl_fr fr on cs.enquiryid=fr.enquiryid inner join tbl_enquiryregister er on fr.enquiryid=er.enquiryid where cs.status='A' and cs.Approval_status='Approved' and costsheet_status='Pending Quotation' order by cs.costsheet_id asc

    Paul.S

  • #767531
    Hi Paul,

    That means you missed some condition while joining 4th table, please review your query and make sure the joining conditions should be correct and use whatever the columns you want in select statements due to this also it may effect duplicate entries.

    col1 col2
    1 2
    1 3
    1 4


    In the above scenario, 1 repeated 3 times in col1, if you consider that only col1 but actual thing is due to col2 entries it's repeated more than once, in your case also the same problem I guess, so make sure about columns selection.

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

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

  • #767534
    Hi

    When you post table structure you must need sample data then only easily identify issue from your Query or data. Because i can do sample data and query taking time also some body no interest on that so. Then your table script also some field not correct status fields you did not mention your Sql Query.

    I created sample Data and executed your query working well check and let me know.

    Sample Data



    INSERT INTO TBL_QUOTATION VALUES('Q-1001','CS-1001',1001,1,GETDATE(),'VAL1','VALREF1','REMARK1','PENDING APPROVAL','A')
    INSERT INTO TBL_QUOTATION VALUES('Q-1002','CS-1002',1002,2,GETDATE(),'VAL2','VALREF2','REMARK2','PENDING APPROVAL','B')
    INSERT INTO TBL_QUOTATION VALUES('Q-1003','CS-1003',1003,3,GETDATE(),'VAL3','VALREF3','REMARK3','PENDING NOT APPROVAL','A')
    INSERT INTO TBL_QUOTATION VALUES('Q-1004','CS-1004',1004,4,GETDATE(),'VAL4','VALREF4','REMARK4','PENDING APPROVAL','A')
    INSERT INTO TBL_QUOTATION VALUES('Q-1005','CS-1004',1005,5,GETDATE(),'VAL5','VALREF5','REMARK5','PENDING APPROVAL','A')


    INSERT INTO TBL_COSTSHEET_WITHOUTCOMPONENT VALUES('CS-1001',1001,1,'ProdFac11','Quref1','REMARK1')
    INSERT INTO TBL_COSTSHEET_WITHOUTCOMPONENT VALUES('CS-1002',1002,2,'ProdFac12','Quref2','REMARK2')
    INSERT INTO TBL_COSTSHEET_WITHOUTCOMPONENT VALUES('CS-1003',1003,3,'ProdFac13','Quref3','REMARK3')
    INSERT INTO TBL_COSTSHEET_WITHOUTCOMPONENT VALUES('CS-1004',1004,4,'ProdFac14','Quref4','REMARK4')
    INSERT INTO TBL_COSTSHEET_WITHOUTCOMPONENT VALUES('CS-1005',1005,5,'ProdFac15','Quref5','REMARK5')


    INSERT INTO TBL_FR VALUES(1,1001,'BoardName1','Boardshape1')
    INSERT INTO TBL_FR VALUES(2,1002,'BoardName2','Boardshape2')
    INSERT INTO TBL_FR VALUES(3,1003,'BoardName3','Boardshape3')
    INSERT INTO TBL_FR VALUES(4,1004,'BoardName4','Boardshape4')
    INSERT INTO TBL_FR VALUES(5,1005,'BoardName5','Boardshape5')

    INSERT INTO TBL_ENQUIRYREGISTER VALUES(1001,'Tester1','EnqName-1','Enqadd-1','647777','C1','123456','CCODE1','CCODE27','PHNOE21','EMAIL1','EMAIL2','REMARK1','TEST4','')
    INSERT INTO TBL_ENQUIRYREGISTER VALUES(1002,'Tester2','EnqName-2','Enqadd-2','647778','C2','123457','CCODE2','CCODE21','PHNOE2','EMAIL12','EMAIL21','REMARK13','TEST3','')
    INSERT INTO TBL_ENQUIRYREGISTER VALUES(1003,'Tester3','EnqName-3','Enqadd-3','647779','C3','123458','CCODE3','CCODE211','PHNOE22','EMAIL13','EMAIL23','REMARK41','TEST2','')
    INSERT INTO TBL_ENQUIRYREGISTER VALUES(1004,'Tester4','EnqName-4','Enqadd-4','647770','C4','123459','CCODE4','CCODE211','PHNOE24','EMAIL14','EMAIL24','REMARK14','TEST1','')
    INSERT INTO TBL_ENQUIRYREGISTER VALUES(1005,'Tester5','EnqName-5','Enqadd-5','647777','C5','123454','CCODE14','CCODE1211','PHNOE124','EMAIL124','EMAIL243','REMARK141','TEST133','')



    your Query



    SELECT * FROM TBL_QUOTATION T1 INNER JOIN TBL_COSTSHEET_WITHOUTCOMPONENT
    T2 ON T1.ENQUIRYID=T2.ENQUIRYID INNER JOIN TBL_FR T3 ON T3.ENQUIRYID=T2.ENQUIRYID INNER JOIN
    TBL_ENQUIRYREGISTER T4 ON T4.ENQUIRYID=T3.ENQUIRYID WHERE T1.STATUS='A' AND
    T1.APPROVAL_STATUS='PENDING APPROVAL' ORDER BY T1.QUOTATION_ID ASC




    Output this



    1 Q-1001 CS-1001 1001 1 Aug 12 2016 9:39AM VAL1 VALREF1 REMARK1 PENDING APPROVAL A 1 CS-1001 1001 1 ProdFac11 Quref1 REMARK1 1 1 1001 BoardName1 Boardshape1 1 1001 Tester1 EnqName-1 Enqadd-1 647777 C1 123456 CCODE1 CCODE27 PHNOE21 EMAIL1 EMAIL2 REMARK1 TEST4
    4 Q-1004 CS-1004 1004 4 Aug 12 2016 9:39AM VAL4 VALREF4 REMARK4 PENDING APPROVAL A 4 CS-1004 1004 4 ProdFac14 Quref4 REMARK4 4 4 1004 BoardName4 Boardshape4 4 1004 Tester4 EnqName-4 Enqadd-4 647770 C4 123459 CCODE4 CCODE211 PHNOE24 EMAIL14 EMAIL24 REMARK14 TEST1
    5 Q-1005 CS-1004 1005 5 Aug 12 2016 9:39AM VAL5 VALREF5 REMARK5 PENDING APPROVAL A 5 CS-1005 1005 5 ProdFac15 Quref5 REMARK5 5 5 1005 BoardName5 Boardshape5 5 1005 Tester5 EnqName-5 Enqadd-5 647777 C5 123454 CCODE14 CCODE1211 PHNOE124 EMAIL124 EMAIL243 REMARK141 TEST133




    This is Working Correct . Check and let me know.

    Each column of the table Displays 3 or 4 times .

    Solution:

    1.you can use group aggregation
    2.all records matching our criteria so returns like this

    3. you can mention which field do you need like this below query i altered.

    SELECT t1.QuotationNumber,t1.CostSheetNumber,t1.EnquiryId FROM TBL_QUOTATION T1 INNER JOIN TBL_COSTSHEET_WITHOUTCOMPONENT
    T2 ON T1.ENQUIRYID=T2.ENQUIRYID INNER JOIN TBL_FR T3 ON T3.ENQUIRYID=T2.ENQUIRYID INNER JOIN
    TBL_ENQUIRYREGISTER T4 ON T4.ENQUIRYID=T3.ENQUIRYID WHERE T1.STATUS='A' AND
    T1.APPROVAL_STATUS='PENDING APPROVAL' ORDER BY T1.QUOTATION_ID ASC

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767535
    Have you tried with 'OUTER APPLY'
    The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. As you might have guessed, the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.
    check below link for more details:
    https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

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

  • #767538
    i think you are done lot of works for me . i am appreciate you.am sorry for this inconvinence.
    thanks thanks am not Mr.Dotnet Developer. am very exciting about your Responsibility.
    i will check and let you know.
    always dotnet our forum very helpful for me.

    thanks with
    Paul.S

  • #767560
    Hi Paul,

    Columns are getting repeated because same column name appears in multiple tables. Your query is absolutely correct. Only thing you missed is - Instead of using * in "Select * from ....." use column name and for same column names in multiple tables use Alias name for that column using "AS" keyword. Try this below query once -
    "Select TBL_QUOTATION.quotation_id, TBL_QUOTATION.QuotationNumber,
    TBL_QUOTATION.CostSheetNumber as Quotation_CostSheetNumber,
    TBL_QUOTATION.enquiryid as Quotation_enquiryid,
    TBL_QUOTATION.fr_id as Quotation_fr_id,
    TBL_QUOTATION.QuotationDate,
    TBL_QUOTATION.QuotationValidity,
    TBL_QUOTATION.QuoteReference as Quotation_QuoteReference,
    TBL_QUOTATION.remarks as Quotation_remarks,
    TBL_COSTSHEET_WITHOUTCOMPONENT.costsheet_id,
    TBL_COSTSHEET_WITHOUTCOMPONENT.costsheetnumber as CostSheet_costsheetnumber,
    TBL_COSTSHEET_WITHOUTCOMPONENT.enquiryid as CostSheet_enquiryid,
    TBL_COSTSHEET_WITHOUTCOMPONENT.fr_id as CostSheet_fr_id,
    TBL_COSTSHEET_WITHOUTCOMPONENT.ProductionFacility,
    TBL_COSTSHEET_WITHOUTCOMPONENT.QuoteReference as CostSheet_QuoteReference,
    TBL_COSTSHEET_WITHOUTCOMPONENT.remarks as CostSheet_remarks,
    TBL_FR.id as FR_id,
    TBL_FR.fr_id as FR_fr_id,
    TBL_FR.enquiryid as FR_enqiryid,
    TBL_FR.boardname,
    TBL_FR.boardshape,
    TBL_ENQUIRYREGISTER.id as Register_id,
    TBL_ENQUIRYREGISTER.enquiryid as Register_enquiryid,
    TBL_ENQUIRYREGISTER.designation,
    TBL_ENQUIRYREGISTER.enquirername,
    TBL_ENQUIRYREGISTER.enquirer_billing_address,
    TBL_ENQUIRYREGISTER.postalcode,
    TBL_ENQUIRYREGISTER.countrycode,
    TBL_ENQUIRYREGISTER.citycode,
    TBL_ENQUIRYREGISTER.phoneno,
    TBL_ENQUIRYREGISTER.countrycode2,
    TBL_ENQUIRYREGISTER.citycode2,
    TBL_ENQUIRYREGISTER.phoneno2,
    TBL_ENQUIRYREGISTER.emailid,
    TBL_ENQUIRYREGISTER.emailid2,
    TBL_ENQUIRYREGISTER.remarks as Register_Remarks,
    TBL_ENQUIRYREGISTER.lead_time_to_delivery
    from ........"

    Hope this will work fine.


Sign In to post your comments