Matching Values with their relevant Types

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

SQL Server Development (2000)

Matching Values with their relevant Types

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 05:04:10

Hi
I wonder if you could help with this problem:

I have following Table structures:


AttributeTypes:
AttributeTypeID  AttributeNames 
1                   Name1            
2                   Name2


AttributeValues:
AttributeValueID  AttributeTypeID AttributeValue
1                   1              Val1  
2                   2              Val2
3                   1              Val3
4                   2              Val4


ProductAttributeValues:
ProductAttributeID  ProductID   AttributeValueID
1                   Prod1       1
2                   Prod1       2
3                   Prod2       3
4                   Prod2       4

Now, I want to write a SP that returns ProductIDs where
(AttributeValueID = 3 AND AttributeTypeID = 1) AND (AttributeValueID = 4 aa.AttributeTypeID = 2)

You see what I am trying to do. I am trying to match the Attribute Values with their relevant Attribute Types

I wrote the following:
SELECT *
FROM ProductAttributeValues pat INNER JOIN
AttributeValues aa ON pat.AttributeValueID = aa.AttributeValueID
WHERE (aa.AttributeValueID = 3) AND (aa.AttributeTypeID = 1) AND (aa.AttributeValueID = 4) AND (aa.AttributeTypeID = 2)

But of course it does not return anything. Any help appreciated. Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-10 : 05:12:10

Try this..
SELECT *
FROM ProductAttributeValues pat INNER JOIN
AttributeValues aa ON pat.AttributeValueID = aa.AttributeValueID
Where
(aa.AttributeValueID = 3 AND aa.AttributeTypeID = 1) OR (aa.AttributeValueID = 4 and aa.AttributeTypeID = 2)

szgldt
Starting Member

10 Posts

Posted - 2006-02-10 : 05:12:21

I think you need an OR rather than an AND. The Attribute value can never be equal to 3 and 4. Hence why you get no rows.

WHERE (aa.AttributeValueID = 3 AND aa.AttributeTypeID = 1) OR (aa.AttributeValueID = 4 AND aa.AttributeTypeID = 2)

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 05:43:55

Thnaks.

I think I really need AND because I want to return results where AttributeValueID is 3 & 4

If I use OR, I will get results where AttributeValueID is 3 BUT perhaps not 4

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-10 : 05:53:20

Why not just use in?


WHERE aa.AttributeValueID in (1,2,3,4)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-10 : 05:54:57

quote:
Originally posted by hasanali00

Thnaks.

I think I really need AND because I want to return results where AttributeValueID is 3 & 4

If I use OR, I will get results where AttributeValueID is 3 BUT perhaps not 4

Oh, and this assumption is incorrect, you will get where it is 3 or 4, think of it when you say it, or means you want both, but either will be sufficient..

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-10 : 05:57:17

Hi,
If you Use an AND clause it will never return a row
because one field can never have both the values it can either have 1st or 2nd
the query specified above will return 2 rows :
1 for attributeid 3 and typeid 1
and 2nd for attributeid 4 and typeid 2

Result:
3 Prod2 3 3 1 Val3
4 Prod2 4 4 2 Val4

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 07:07:00

Is there a way to create the following dynamic temporary table from my given tables:


TempTable:
ID                  ProductID   ValueForType1   ValueForType2
1                   Prod1       1                2
2                   Prod2       3                4

Basically, ValueForType1 & ValueForType2 are new columns that will hold AttributeValues from ProductAttributeValues table.

I believe if I can create the above table, i will be able to get a products where ValueForType1=3 & ValueForType2=4

But I just don't know how I can create this type of table from my given table structure

Kind regards

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-10 : 07:57:59

What I think you're saying hasanali00 is that you only want to return those products that have certain combinations of attributes. There are various ways of achieving this depending upon the specifics of your data. Will there always be just pairs of attributes types? Are you only interested in the same pairs of attribute values (i.e. 1 and 2 or 3 and 4)? If this is the case, then your idea to construct a denormalised dataset is sound. You could do something like:


SELECT pav.*
FROM
(
SELECT
	pav.ProductID, 
	MAX(av_1.AttributeValue) AS ValueForType1, 
	MAX(av_2.AttributeValue) AS ValueForType2
FROM 
	dbo.ProductAttributeValues AS pav
	LEFT JOIN dbo.AttributeValues AS av_1
		ON pav.AttributeValueID = av_1.AttributeValueID
			AND av_1.AttributeTypeID = 1
LEFT JOIN dbo.AttributeValueID AS av_2 
	ON pav.AttributeValueID = av_2.AttributeValueID
		AND av_2.AttributeTypeID = 2
GROUP BY 
	pav.ProductID
) AS pav
WHERE 
	(pav.ValueForType1 = 1 AND pav.ValueForType2 = 2)
	OR 
	(pav.ValueForType1 = 3 AND pav.ValueForType2 = 4)

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources