SQL Statement Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Statement Problem

Author

Topic

fredriko
Starting Member

3 Posts

Posted - 2006-02-08 : 05:36:04

I have three tables defined as follows


PRODUCTS (ProductId, ProductName)
LOCATIONS (LocationId, LocationName)
LOCATIONPRODUCTS (LocationId, ProductId, Min, Max)

I can return a list of the products associated with a given location of 1 as such

SELECT * FROM LOCATIONPRODUCTS WHERE LocationId=1

The problem I am having is that I cannot seem to write a sql statement that will, for a given location id, return every product available i.e. List all products with a min and max value in the LOCATIONPRODUCTS table, but also list all the unique products that do not have an entry in this table and default their min, max values to NULL.
Does anybody know how I might accomlish this? I can include data samples If you want me to expand on this rather complicated question?

Its Absurd and I don't take part in Absurdities!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-08 : 06:04:39

Hi fredriko,
Use Left Outer join
Select *
from Products
left outer join Locationproducts
on products.productid = Locationproducts.productid
where LocationId=1

fredriko
Starting Member

3 Posts

Posted - 2006-02-08 : 06:31:45

Thanks for the reply but unfortunately this isn't quite what i'm after. Maybe a sample will help
Assuming the tables contain the following data


PRODUCTS Table
ProductId     ProductName
-------------------------------------
01            Test Product One
02            Test Product Two
03            Test Product Three
04            Test Product Four

LOCATION Table
LocationId     LocationName
-------------------------------------
01             Test Location One
02             Test Location Two

LOCATIONPRODUCTS Table
LocationId     ProductId   Max  Min
-------------------------------------
01             02          10   5
01             03          4    2

Passing a LocationId of 1 i am hoping to return the following results


LocationId      ProductId      Max     Min
-------------------------------------------------
01            01            0       0
01              02             10      5
01              03             4       2
01            04            0       0

The lines highlighted is bold do not exist in the LOCATIONPRODUCTS table but should be included in the results. It would be easier to create entries in the LOCATIONPRODUCTS table every time a product and/or location is created but this would result in a very large table being produced and could eventually lead to a slow application.
I hope this helps clarify my dilema.

Its Absurd and I don't take part in Absurdities!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-08 : 07:04:10

try this

select b.locationid, a.productid, isnull([max],0) ,isnull([min],0) from products a
left outer join ( select * from LOCATIONPRODUCTS where locationid ='01' )b
on a.productid = b.productid

fredriko
Starting Member

3 Posts

Posted - 2006-02-08 : 07:42:47

Almost. The only problem now being that rows where products do not exist in the LOCATIONPRODUCTS table now contain NULL as the location id not 7?
I have been messing around with the query analyser and have come up with the following statement that pretty much does what I want.


SELECT A.LocationId, A.ProductId, B.ProductName, A.MinCount, A.MaxCount
FROM  tblLocationProducts AS A
INNER JOIN tblProducts AS B ON A.ProductId = B.ProductId
WHERE A.LocationId = 7

UNION ALL

SELECT 7 as LocationId, C.ProductId, C.ProductName, 0 as MaxCount, 0 as MinCount 
FROM tblProducts AS C
WHERE NOT C.ProductId IN (Select D.ProductId FROM tblLocationProducts as D WHERE D.LocationId=7)

ORDER BY A.LocationId, A.ProductId

If you have a more elegant/better solution I'd like to see it.

Its Absurd and I don't take part in Absurdities!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 07:52:23

use CROSS JOIN

select 	l.LocationID, p.ProductID, isnull(lp.Max, 0), isnull(lp.Min, 0)
from	LOCATION l cross join PRODUCTS p
	left join LOCATIONPRODUCTS lp
	on	l.LocationID	= lp.LocationID
	and	p.ProductID	= lp.ProductID
where	l.LocationID	= '01'

----------------------------------
'KH'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-08 : 09:47:51

quote:

Originally posted by khtan

use CROSS JOIN

select 	l.LocationID, p.ProductID, isnull(lp.Max, 0), isnull(lp.Min, 0)
from	LOCATION l cross join PRODUCTS p
	left join LOCATIONPRODUCTS lp
	on	l.LocationID	= lp.LocationID
	and	p.ProductID	= lp.ProductID
where	l.LocationID	= '01'

----------------------------------
'KH'

you've made me very happy! Well done.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-08 : 22:58:59

quote:
Originally posted by jsmith8858

you've made me very happy! Well done.

Thank you. I learn the trick from a certain Dr. C. J.

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources