multiple joins, 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

General SQL Server Forums

New to SQL Server Programming

multiple joins, sql query

Author

Topic

knudmt
Starting Member

12 Posts

Posted - 2011-05-04 : 21:31:44

So still a noob :( And I am having a hard time performing a proper join on these tables. Could someone shed some light on this?

Thanks!

here is my db schema

FileTbl
guidId int PK

Attributes Table
id PK
guidId FK ref FileTble(guidid)
name varchar

AttributeValues table
attId int FK ref Attributes(id)
setValueId int FK ref setvalue(id)
exactValue bit
guidId int FK FileTbl(guidId)

setvalue
id int PK
value varchar

Example of how these tables might look:

FileTbl
145
267

Attributes
1, 145, language
2, 145, length
3, 267, language

AttributeValues
1, 10, false, 145
2, 11, false, 145
3, 23, false, 145

SetValue
10, en-us
11, long
23, en-ca

So I would like a query that would give me the following values:

The Attributes.name and SetValue.value for a given FileTbl.guidId

ie. return a table showing ALL the Attributes.name and SetValue.value for a single FileTbl.guidId

If someone could be so kind to give me an example of how this would be done and I would be super excited if you could explain your logic :)

I'm in a bit of a tight spot your help would be greatly appreciated!

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-04 : 22:09:25

EAV model! Many people on this forum would warn you about the pitfalls and predicaments you may get into with that. But, I am not here to lecture you (although it is very tempting

), so see if this works for you:

select
   at.name,
   sv.value
from
   attributes at
   inner join attributevalues av
      on av.attId = at.id
   inner join SetValue sv
      on sv.id = av.SetValueId

knudmt
Starting Member

12 Posts

Posted - 2011-05-04 : 22:18:59

please feel free to lecture!!! If you can poke holes in the schema feel free. :) not just looking for answers but insight :)

Thanks again sunitabeck when I get around my dev machine I will try this out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources