Problems with joins creating more rows

[collierd]

Hello

Can anybody help
I am using SQL 2000 and attempting to run a query which drags info from 3 tables:

select  pr.productdesc, pd.*, np.pricesuom as 'NORTHPCSUOM', sp.pricesuom as 'SOUTHPCSUOM' from product_duplications pd
left outer join product pr on pd.productid = pr.productid
left outer join nprice np on dbo.CreatePriceId(pd.productid) = np.priceid
left outer join sprice sp on dbo.CreatePriceId(pd.productid) = sp.priceid

Basically some products have data held which is different from north to south and I want to find these to correct them

When I run this as a straight forward select from product_duplications (i.e. no joins) it selects 661 items

If I join just nprice it returns 665
If I then add sprice it returns 675
If I then add product it returns 890

If I change the statment to have a where at the bottom

i.e.

where dbo.CreatePriceId(pd.productid) = np.priceid
and dbo.CreatePriceId(pd.productid) = sp.priceid
and pd.productid = pr.productid

It returns 827 rows

All I want is for it to show 661 rows along with the additional data from the other tables alongside (i.e. the 661 rows with additional columns)

Suspect it is something to do with my join policy but not sure what the problem is

Can anybody help please

Thanks

Damian.

 

[r937]

no idea without seeing the actual data (no, please don't post all 890 products)

what is CreatePriceId? is that some kind of function that generates a price id? you might want to have a look in there, because that's where the extra ids are probably coming from

also, you might try posting your question in the sql/server forum

rudy

 

[collierd]

Could do
CreatePriceId is a function that generates price ids
This works fine on its own
The only thing I will say is that if I remove all references to nprice and sprice which in turn eliminates CreatePriceId it still returns about 750 items
i.e. just query using the left outer join on product

Is the general way this has been approached (left outer joins) correct ?

Damian

 

[r937]

yes, left outr join is the correct approach

if you have 661 rows in product_duplications , but 750 rows when you do a left outer join to the product table, then there are duplications in the product table

you can find them with

select pd.productid, pr.productid
from product_duplications pd
left outer join product pr
on pd.productid = pr.productid
group by pd.productid, pr.productid
having count(*) > 1

 

[collierd]

Thanks for that
Its a useful starting point