Need Urgent help with simple query

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)

Need Urgent help with simple query

Author

Topic

Shiva808
Starting Member

6 Posts

Posted - 2007-04-25 : 19:20:01

Hi, I cant for the life of me work this out.
I have a table of keys like:

LINEUP
======


Vocals  Guitar   Bass   Drums
------  ------   ----   -----
1       2        3      4
5       2        6      5

With the key values in the Member table like:

MEMBER
======


ID  Name
--  ----
1   Joe
2   Greg
3   Paul
4   Mick
5   Dave
6   Bill

Now I need to return the lineup table but with the names not keys like

RETURNED RESULT
===============


Vocals  Guitar   Bass   Drums
------  ------   ----   -----
Joe     Greg     Paul   Mick
Dave    Greg     Bill   Dave

Please how can I do this?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 19:26:12

[code]
select v.Name as Vocals,
g.Name as Guitar,
b.Name as Bass,
d.Name as Drums
from LINEUP l inner join MEMBER v
on l.Vocals = v.ID
inner join MEMBER g
on l.Guitar = g.ID
inner join MEMBER b
on l.Bass = b.ID
inner join MEMBER d
on l.Drums = d.ID
[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 19:28:57

[code]
Declare @keys table ( Vocals int, Guitar int, Bass int, Drums int)
insert into @keys select 1 ,2 ,3, 4 union all
select 5 ,2 ,6 ,5
select * from @keys

declare @MEMBER table (id int, name varchar(50) )
insert into @MEMBER select 1 ,'Joe' union all
select 2 ,'Greg' union all
select 3 ,'Paul' union all
select 4 ,'Mick' union all
select 5 ,'Dave' union all
select 6 ,'Bill'
--select * from @MEMBER

select m.*, m2.* , m3.*, m4.*
from @keys k
join @MEMBER M on k.Vocals = m.id
join @MEMBER M2 on m2.id = k.Guitar
join @MEMBER m3 on m3.id = k.Bass
join @MEMBER m4 on m4.id = k.Drums
[/code]

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Shiva808
Starting Member

6 Posts

Posted - 2007-04-25 : 19:42:05

Awsome guys, thanks very much for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources