Query Help Please - Pulling My Hair Out!

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)

Query Help Please - Pulling My Hair Out!

Author

Topic

donfripp
Starting Member

1 Post

Posted - 2006-07-30 : 19:28:09

I am looking for help with a query, perhaps with a subquery, that eventually will find its way to a stored procedure. I have 3 tables (SETS, ATTRIBUTES, VALUES). Each SET is defined by a type. There are many SETS. Each SET type has a fixed number of ATTRIBUTES. Users may or may not specify VALUES for each of those ATTRIBUTES. So, how do I query to retrieve a list of all the ATTRIBUTES for a SET, the VALUES specified for each ATTRIBUTE for the SET, as well as those ATTRIBUTES where the user did not specifiy a VALUE. Im my app, the user selects a SET, and I will show all the ATTRIBUTES for the SET, with rows for those Attributes needing Values. Do I need to add a record to VALUES for each appropriate ATTRIBUTE, even if the Value is Null?

Result:
SetID
Attribute
Value*

*Might be null

SetID
SetName
SetType

AttributeID
SetType
AttributeName

ValueID
SetID
AttributeID

My thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-30 : 21:34:24

Try this. If it is not what you want, post some sample data and the result that you want.

select s.SetID, a.AttributeID, a.AttributeName, v.ValueID
from   SETS s inner join ATTRIBUTES a
       on   s.SetType =  a.SetType
       left join VALUES v
       on   s.SetID       = v.SetID
       and  a.AttributeID = v.AttributeID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources