join sp problem

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.

Re: join sp problem

Hi,

There are more rows in the tables?

If so, can you post a sample of the data and the expected result?

Re: join sp problem

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

Re: join sp problem

This condition to inner join is correct?

vsp.santu
inner join tbl_PurchageOrder as b on a.seskey = b.userdid

In your table tbl_PurchaseOrder has userdid and tbl_Partnerhirerege has userdid.

Re: join sp problem

Ya, its correct. Because sekey from tbl_Partnerhirerege is the foriegn key(userdid) of remaining tables. PLease not consider userdid in tbl_Partnerhirerege

Re: join sp problem

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.

Re: join sp problem

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

Re: join sp problem

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

Re: join sp problem

Hi,

I tried your code. But this also repeting records

Re: join sp problem

Try include:

select distinct ....

Re: join sp problem

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

Re: join sp problem

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

Re: join sp problem

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

Re: join sp problem

Sorry, missed:

vsp.santu
join tbl_quotation_master qm on ph.seskey = qm.userdid and qm.seskey = q.quotaion_id

can try again ?

Re: join sp problem

Try:

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

Hope this helps.

Re: join sp problem

Hi,'here also record repeating two times

Re: join sp problem

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.

Re: join sp problem

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

Re: join sp problem

I believe that needed in the join with columns seskey (tbl_quotation_master) and quotaion_id (tbl_quotation) for not duplicated.

Re: join sp problem

Hi,

thanks for your efforts