complex select query... confused.

[skygoth]

Hey there.
Here's the basic idea:
table THINGS (thing1type smallint, numthing1 smallint, thing2type smallint, numthing2 smallint, thing3type smallint, numthing3 smallint)
table THING_TYPES (thingnum smallint, attribute1 smallint, attribute2 smallint, attribute3 smallint)
table ATTRIBUTE1_INFO (attributenum smallint, name varchar, nickname varchar, description varchar)
(similar tables exist for each of the 3 attribute types)

I would like to get a query where my result is the nicknames of the attributes:
(thing1.attribute1.nick, thing1.attribute2.nick, thing1.attribute3.nick, numthing1, thing2.attribute1.nick, thing2.attribute2.nick, thing2.attribute3.nick, numthing2,...)

But, for the life of me, I can't figure out how to make that complex of a select statement.

Would someone please make a suggestion? (either how to clean up my database, but preferably, how to select from it as is)

-nathan sheldon

 

[r937]

wow, that's confusing

i am totally guessing here:

select 'thing1'       as whichtype
     , T.thing1type   as thingtype
     , T.numthing1    as numthing
     , TT.attribute1  as attribute
     , A1.name
     , A1.nickname
     , A1.description
  from THINGS as T
inner
  join THING_TYPES as TT
    on T.numthing1
     = TT.thingnum
inner
  join ATTRIBUTE1_INFO as A1
    on TT.attribute1
     = A1.attributenum
union all     
select 'thing2'       as whichtype
     , T.thing2type   as thingtype
     , T.numthing2    as numthing
     , TT.attribute2  as attribute
     , A2.name
     , A2.nickname
     , A2.description
  from THINGS as T
inner
  join THING_TYPES as TT
    on T.numthing2
     = TT.thingnum
inner
  join ATTRIBUTE2_INFO as A2
    on TT.attribute2
     = A2.attributenum
union all     
select 'thing3'       as whichtype
     , T.thing3type   as thingtype
     , T.numthing3    as numthing
     , TT.attribute3  as attribute
     , A3.name
     , A3.nickname
     , A3.description
  from THINGS as T
inner
  join THING_TYPES as TT
    on T.numthing3
     = TT.thingnum
inner
  join ATTRIBUTE3_INFO as A3
    on TT.attribute3
     = A1.attributenum

rudy

http://r937.com/

SQL Consulting