I create a sp using above four tables join. When i executing that sp then record repeating more than three times
And my sp is
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[SP_PURCHAGE_JOIN]
@company INT
as
select a.companyname,upper(a.shortname)+replace(convert(varchar,convert(datetime,d.quotation_date),1),'/','')+
convert(varchar,d.seskey) as
[QuotationID],a.seskey,b.quotaion_id,b.Purchage_No,b.Purchage_Date,b.Purchage_Amount,b.Upload,b.Upload_Filename
from tbl_partnerhirerege as a
inner join tbl_PurchageOrder as b on a.seskey = b.userdid inner join tbl_quotation as c on c.userdid = b.userdid
inner join tbl_quotation_master as d on d.seskey = c.quotaion_id where a.seskey=@company
Anybody please help me. I am waitning for your reply.
Ya, its correct. Because sekey from tbl_Partnerhirerege is the foriegn key(userdid) of remaining tables. PLease not consider userdid in tbl_Partnerhirerege
In your tables has records for to return all informations? You problem maybe is that any table not have some records. Maybe alter inner join for left join in some relation should to resolve your problem.
Kindly mark this post as "Answer", if it helped you.
vsp.santu
Member
156 Points
421 Posts
join sp problem
Apr 23, 2012 11:08 AM|LINK
Hi,
My tables are
Table1: tbl_Partnerhirerege
seskey userdid companyname shortname
12 14 CISCO CIS
Table2: tbl_PurchaseOrder
seskey userdid quotaion_id pono poamount
1 12 40 cis001 120
Table3: tbl_quotation
seskey userdid quotaion_id rate
1 12 40 1000
Table4: tbl_quotation_master
seskey userdid quotation_date
40 12 04/23/2012
I create a sp using above four tables join. When i executing that sp then record repeating more than three times
And my sp is
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[SP_PURCHAGE_JOIN]
@company INT
as
select a.companyname,upper(a.shortname)+replace(convert(varchar,convert(datetime,d.quotation_date),1),'/','')+
convert(varchar,d.seskey) as
[QuotationID],a.seskey,b.quotaion_id,b.Purchage_No,b.Purchage_Date,b.Purchage_Amount,b.Upload,b.Upload_Filename
from tbl_partnerhirerege as a
inner join tbl_PurchageOrder as b on a.seskey = b.userdid inner join tbl_quotation as c on c.userdid = b.userdid
inner join tbl_quotation_master as d on d.seskey = c.quotaion_id where a.seskey=@company
Anybody please help me. I am waitning for your reply.
Thank you.
imobsuz
Participant
1278 Points
195 Posts
Re: join sp problem
Apr 23, 2012 11:45 AM|LINK
Hi,
There are more rows in the tables?
If so, can you post a sample of the data and the expected result?
vsp.santu
Member
156 Points
421 Posts
Re: join sp problem
Apr 23, 2012 12:10 PM|LINK
Hi,
My sample data of the four tables
Table1: tbl_Partnerhirerege
seskey(PK) userdid(FK) companyname shortname
12 14 YAHOO YAH
13 15 CISCO CIS
Table2: tbl_PurchaseOrder
seskey(PK) userdid(FK) quotaion_id pono poamount
1 12 40 cis001 120
2 12 40 YAH001 100
Table3: tbl_quotation
seskey(PK) userdid(FK) quotaion_id rate
1 12 40 1000
2 12 40 1200
Table4: tbl_quotation_master
seskey(PK) userdid(FK) quotation_date
40 12 04/23/2012
41 13 04/22/2012
My expected result from four tables
Companyname QuotationID Purchaseno Purchaseamount Upload Upload_Filename
YAHOO YAH04231240 YAH001 100 <BYTE> PROFILE
CISCI CIS04221241 CIS001 100 <BYTE> PROFILE
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 12:25 PM|LINK
Hi
This condition to inner join is correct?
In your table tbl_PurchaseOrder has userdid and tbl_Partnerhirerege has userdid.
vsp.santu
Member
156 Points
421 Posts
Re: join sp problem
Apr 23, 2012 12:32 PM|LINK
Ya, its correct. Because sekey from tbl_Partnerhirerege is the foriegn key(userdid) of remaining tables. PLease not consider userdid in tbl_Partnerhirerege
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 12:36 PM|LINK
In your tables has records for to return all informations? You problem maybe is that any table not have some records. Maybe alter inner join for left join in some relation should to resolve your problem.
vsp.santu
Member
156 Points
421 Posts
Re: join sp problem
Apr 23, 2012 12:46 PM|LINK
Table1: tbl_Partnerhirerege
seskey(PK) userdid(FK) companyname shortname
12 14 YAHOO YAH
13 15 CISCO CIS
Table2: tbl_PurchaseOrder
seskey(PK) userdid(FK) quotaion_id(fk) pono poamount upload upload_filename
1 12 40 cis001 120 <byte> profile
2 12 40 YAH001 100 <byte> profile
Table3: tbl_quotation
seskey(PK) userdid(FK) quotaion_id(fk) rate
1 12 40 1000
2 12 40 1200
Table4: tbl_quotation_master
seskey(PK) userdid(FK) quotation_date
40 12 04/23/2012
41 13 04/22/2012
Here seskey in tbl_Partnerhirerege is the foriegn key of tbl_PurchaseOrder, tbl_quotation and tbl_quotation_master.
And seskey in tbl_quotation_master is the foriegn key of tbl_PurchaseOrder and tbl_quotation
Now i should get output data based on userdid.
For example:
@company=12
Companyname QuotationID Purchaseno poamount upload upload_filename
YAHOO YAH042312 YAH001 1000 <byte> profile
Here QuotationID is shortname from tbl_Partnerhirerege + Date from tbl_quotation_master
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 12:56 PM|LINK
Can try:
select ph.companyname,upper(ph.shortname)+replace(convert(varchar,convert(datetime,qm.quotation_date),1),'/','')+
convert(varchar,qm.seskey) as
[QuotationID],ph.seskey,
po.quotaion_id,
po.Purchage_No,
po.Purchage_Date,
po.Purchage_Amount,
po.Upload,po.Upload_Filename
from tbl_Partnerhirerege ph
join tbl_PurchaseOrder po on ph.seskey = po.userdid
join tbl_quotation q on ph.seskey = q.userdid
join tbl_quotation_master qm on ph.seskey = qm.userdid
where ph.seskey = 12 -- @company
vsp.santu
Member
156 Points
421 Posts
Re: join sp problem
Apr 23, 2012 01:02 PM|LINK
Hi,
I tried your code. But this also repeting records
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 01:04 PM|LINK
Try include:
select distinct ....