Vertical Data Where Clause - 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)

Vertical Data Where Clause

Author

Topic

martalex
Starting Member

17 Posts

Posted - 2003-12-05 : 10:21:57

I have the following tables. How can I find all the products available in Color 'Black' and Size 'Large'. Since the attribute data is vertical instead on horizontal I'm not sure how to accomplish this.

Does this query make sense?

SELECT prod_id
FROM tbl_products
WHERE prod_id IN (
SELECT prod_id
FROM tbl_products_attributes
GROUP BY prod_id, optval_id
HAVING optval_id In (7,10) AND Count(prod_attr_id)>=2
)

tbl_products (prod_id, prod_name)
p1, Jacket1
p2, Jacket2
p3, Jacket3

tbl_options (option_id, option_desc)
o1, Color
o2, Size

tbl_options_values (optval_id, option_id, optval_name)
v1, o1, Blue
v2, o1, Black
v3, o1, Green
v4, o2, Large
v5, o2, Small

tbl_products_attributes (prod_attr_id, prod_id, optval_id, prod_attr_name, prod_attr_price)
a1,p1,v1,10
a2,p1,v2,0
a3,p1,v4,0
a4,p2,v2,12
a5,p2,v3,0
a6,p2,v4,0
a7,p2,v5,10

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-05 : 10:34:27

Close, I think this should work:

SELECT 	prod_id 
FROM 	tbl_products
WHERE 	prod_id IN 
	(
	SELECT	prod_id
	FROM	tbl_products_attributes
	WHERE	optval_id In (7,10) --- Isn't it ('v2', 'v4')?
	GROUP BY prod_id
	HAVING COUNT(prod_attr_id)>=2
	)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-05 : 10:56:36

To be thorough, I would be more explicit to ensure the attribute is the one you mean to check; maybe more than 1 attribute might have a value of "Large", for example.

So you would need to say something like:

where (option_Desc = 'Color' and optval_name = 'Black') OR
(option_Desc = 'Size' and optval_name = 'Large')

and don't forget the HAVING COUNT(*) =2 !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources