Category: SQL Server

11 Aug 2016 00:07

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	2

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

11 Aug 2016 03:33

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	1

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

11 Aug 2016 04:13

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

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

11 Aug 2016 04:41

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

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

11 Aug 2016 04:51

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

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

11 Aug 2016 04:57

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

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

11 Aug 2016 21:19

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	4

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

11 Aug 2016 23:55

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

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

12 Aug 2016 02:02

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	1

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

16 Aug 2016 14:51

Joined:	16/08/2016
Level:	Bronze
Points:	8

Points:	4

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.

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

About dotnetspider.com

Quick Links

General

Services