from tbl_partnerhirerege as ph
inner join tbl_quotation_master as qm on qm.userdid = ph.seskey
inner join tbl_PurchageOrder as po on po.quotation_id = qm.seskey
inner join tbl_quotation as q on q.quotation_id = qm.seskey
where ph.seskey = @company
For the sample that you posted there is only one row in the table tbl_quotation_master, with column userdid equal to 12, so the result should show only one line.
Thank you both of you. Its working. I gave like below
select distinct 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 as ph
inner join tbl_quotation_master as qm on qm.userdid = ph.seskey
inner join tbl_PurchageOrder as po on po.quotaion_id = qm.seskey
inner join tbl_quotation as q on q.quotaion_id = qm.seskey
where ph.seskey = 13
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 01:06 PM|LINK
try too:
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 and q.quotaion_id = po.quotaion_id
join tbl_quotation_master qm on ph.seskey = qm.userdid and seskey = q.quotaion_id
where ph.seskey = 12 -- @company
vsp.santu
Member
160 Points
434 Posts
Re: join sp problem
Apr 23, 2012 01:15 PM|LINK
Hi, i gave distinct here als repetaing,
Output coming like below
Companyname QuotationID seskey quotation_id Purchageno Poamount upload
yahoo yah04011240 12 40 1 100 <byte>
yahoo yah04011242 12 40 1 100 <byte>
But here the difference is only QuotationID. There is first record showing yah04011240 and for second record yah04011242 .
I dont why this showing like this.
Actually QuotationID is shortname(yah) from tbl_Partnerhirrege and date(040112) and 40(seskey) from tbl_quotation_master.
Please solve this problem
vsp.santu
Member
160 Points
434 Posts
Re: join sp problem
Apr 23, 2012 01:18 PM|LINK
hi,
join tbl_quotation_master qm on ph.seskey = qm.userdid and seskey = q.quotaion_id
here seskey is showing invalid column error when i execute
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 01:21 PM|LINK
Sorry, missed:
can try again ?
imobsuz
Participant
1278 Points
195 Posts
Re: join sp problem
Apr 23, 2012 01:24 PM|LINK
Try:
Hope this helps.
vsp.santu
Member
160 Points
434 Posts
Re: join sp problem
Apr 23, 2012 01:27 PM|LINK
Hi,'here also record repeating two times
imobsuz
Participant
1278 Points
195 Posts
Re: join sp problem
Apr 23, 2012 01:32 PM|LINK
You can check the sample data from the tables?
For the sample that you posted there is only one row in the table tbl_quotation_master, with column userdid equal to 12, so the result should show only one line.
vsp.santu
Member
160 Points
434 Posts
Re: join sp problem
Apr 23, 2012 01:34 PM|LINK
Hi,
Thank you both of you. Its working. I gave like below
select distinct 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 as ph
inner join tbl_quotation_master as qm on qm.userdid = ph.seskey
inner join tbl_PurchageOrder as po on po.quotaion_id = qm.seskey
inner join tbl_quotation as q on q.quotaion_id = qm.seskey
where ph.seskey = 13
pierrefrc
Participant
947 Points
201 Posts
Re: join sp problem
Apr 23, 2012 01:38 PM|LINK
I believe that needed in the join with columns seskey (tbl_quotation_master) and quotaion_id (tbl_quotation) for not duplicated.
vsp.santu
Member
160 Points
434 Posts
Re: join sp problem
Apr 23, 2012 01:40 PM|LINK
Hi,
thanks for your efforts