Sql Query - 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 2005 Forums

Transact-SQL (2005)

Sql Query

Author

Topic

vividearth
Starting Member

2 Posts

Posted - 2009-02-04 : 11:55:06

Hi

I am trying to optimise a query we have and am getting a bit stuck. I have a table with AssociationId, AttributeType and AttributeId columns. I am trying make this work with a filter menu on the attributes so a user can build up their select as Color = Red AND Size = Large etc.

I would have a stored procedure passing in @Color, @Size, etc and so need a way to build the query. The only way I can think of is something like:

SELECT AssociationId
FROM Attributes
WHERE AssociationId IN (
((AttributeType ='Color' AND AttributeId = @Color) OR @Color = 0)
) AND AssociationId IN (
((AttributeType ='Size' AND AttributeId = @Size) OR @Size= 0)
)

However this delivers very poor performance with many records and attribute types.

Does anyone have any better method of achieving this?

Thanks for your help.
v

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:02:09

seems like what you need is


SELECT AssociationId
FROM
(
SELECT AssociationId,
MAX(CASE WHEN AttributeType ='Color' THEN AttributeId ELSE NULL END) AS ColorID,
MAX(CASE WHEN AttributeType ='Size' THEN AttributeId ELSE NULL END) AS SizeID
FROM Attributes
GROUP BY AssociationId
)t
WHERE (ColorID = @Color OR @Color = 0)
AND (SizeID=@Size OR @Size= 0)