multiple fields from common lookup table

I haven't written SQL in some time and I'm extremely rusty, so help
would be appreciated and thanks in advance.
I have a table (main) that has multiple fields that contain a lookup id
to a common lookup table (lookup). How do I write the query to pull
back one result set that has the form below?
select ?
where main.key = @.key
result set
--
key (from main)
behavior description (from lookup)
appearance description (from lookup)
other description (from lookup)
main
--
key
behaviorID
appearanceID
otherID
lookup
--
lookupID
descriptionPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Sba,
Try:
DECLARE @.KEYVAL INT
SET @.KEYVAL = 1
SELECT KEYVAL, L1.DESCRIPTION AS 'BEHAVIOR', L2.DESCRIPTION AS 'APPEARANCE',
L3.DESCRIPTION AS 'OTHER'
FROM MAIN M
JOIN LOOKUP L1
ON M.BEHAVORID = L1.LOOKUPID
JOIN LOOKUP L2
ON M.APPEARANCEID = L2.LOOKUPID
JOIN LOOKUP L3
ON M.OTHERID = L3.LOOKUPID
WHERE KEYVAL = @.KEYVAL
HTH
Jerry
"sba" <pub9@.s105192480.onlinehome.us> wrote in message
news:1129764599.221796.33700@.g49g2000cwa.googlegroups.com...
>I haven't written SQL in some time and I'm extremely rusty, so help
> would be appreciated and thanks in advance.
> I have a table (main) that has multiple fields that contain a lookup id
> to a common lookup table (lookup). How do I write the query to pull
> back one result set that has the form below?
> select ?
> where main.key = @.key
>
> result set
> --
> key (from main)
> behavior description (from lookup)
> appearance description (from lookup)
> other description (from lookup)
>
> main
> --
> key
> behaviorID
> appearanceID
> otherID
>
> lookup
> --
> lookupID
> description
>|||Looks like you have the famous One True Lookup Table. A classic database
design error.
Anyway, you can join multiple times to the same table:
SELECT M.key, T1.description, T2.description, T3.description
FROM main AS M
JOIN lookup AS T1
ON M.behaviourid = T1.id
JOIN lookup AS T2
ON M.appearanceid = T2.id
JOIN lookup AS T3
ON M.otherid = T3.id ;
David Portas
SQL Server MVP
--