December 22, 2004 at 2:54 pm
Within my database I have a couple tables where one table defines user assigned attributes for records in the other table.
For example, the DataCollections table could have a record "Project Information" that has two Attribute Records in the the Attributes table ("Client Name=My Client" and "Project=Some Project")
I need to find a way to write a search query that returns the matching DataCollections with their attributes as columns. Using the above example, the query result would look like:
CollectionName Client Project
===================================================
Project Information My Client Some Project
Can anyone suggest a way of solving this problem?
Here is my actual table structures, there is a FK relationship between the two tables on the DataCollectionID field:
Table: DataCollections
==============================
PK DataCollectionID INT IDENTITY
CollectionName VARCHAR(50)
Table: CollectionAttributes
==============================
PK DataCollectionID INT
PK AttributeType VARCHAR(50)
AttributeValue VARCHAR(50)
Table: AttributeTypes
=======================
PK AttributeType VARCHAR(50)
Thank you,
Shawn Smiley
December 23, 2004 at 1:25 am
SELECT DC.CollectionName ,
MAX(CASE CA.AttributeType WHEN 1 THEN AttributeValue ELSE '' END) AS Attribute1Name ,
MAX(CASE CA.AttributeType WHEN 2 THEN AttributeValue ELSE '' END) AS Attribute2Name
FROM dbo.CataCollections as DC INNER JOIN dbo.CollectionAttributes AS CA
ON DC.DataCollectionID = CA.DataCollectionID
GROUP BY DC.CollectionName
December 23, 2004 at 6:45 am
Thanks for the info, I thought there was a way to do this. 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply