Getting results from 2 tables, determined by a 3rd

Hi
Suppose I have 2 tables that each includes 1 column of data and 1 column of
some ID, like this
First table
ID FName
1 John
2 Martin
3 Jenny
Second table
ID SName
A King
B Brown
C Andersson
And then a 3rd table that joins these 2 tables together, like this
Third table
ID1 ID2
1 C
2 A
2 C
3 A
3 B
How would I create a query that effectively lists the third table, but
instead of outputting rows with 1 C and 2 A it should give me John Andersson
and Martin King i.e. giving me the relations from table 3 but using the data
from tables 1 and 2.
I am playing with JOIN at the moment but can't seem to make it join twice in
one query (table1.ID with table3.ID1 and table2.ID with table3.ID2). Hope
I've made myself clear
Thanks for any input
Iblb
Try this
create table #t1(id int, FName varchar(10))
create table #t2(id varchar(1), SName varchar(10))
create table #t3(id int,id2 varchar(1))
insert into #t1 values(1,'John')
insert into #t1 values(2,'Martin')
insert into #t1 values(3,'Jenny')
insert into #t2 values('A','King')
insert into #t2 values('B','Brown')
insert into #t2 values('C','Andersson')
insert into #t3 values(1,'C')
insert into #t3 values(2,'A')
insert into #t3 values(2,'C')
insert into #t3 values(3,'A')
insert into #t3 values(3,'B')
select distinct f.fname + ' ' + l.sname
from #t3 t4 inner join
(select t1.id,t1.fname
from #t3 tt3
inner join #t1 t1 on t1.id=tt3.id
) f on f.id=t4.id
inner join
(select t2.id,t2.sname
from #t3 tt3
inner join #t2 t2 on t2.id=tt3.id2
)l on t4.id2=l.id
VT
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:On%23vszPJHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi
> Suppose I have 2 tables that each includes 1 column of data and 1 column
> of some ID, like this
> First table
> ID FName
> 1 John
> 2 Martin
> 3 Jenny
> Second table
> ID SName
> A King
> B Brown
> C Andersson
> And then a 3rd table that joins these 2 tables together, like this
> Third table
> ID1 ID2
> 1 C
> 2 A
> 2 C
> 3 A
> 3 B
> How would I create a query that effectively lists the third table, but
> instead of outputting rows with 1 C and 2 A it should give me John
> Andersson and Martin King i.e. giving me the relations from table 3 but
> using the data from tables 1 and 2.
> I am playing with JOIN at the moment but can't seem to make it join twice
> in one query (table1.ID with table3.ID1 and table2.ID with table3.ID2).
> Hope I've made myself clear
> Thanks for any input
> Ib
>|||Thanks alot your code worked fine.
It was actually a little too fine since I was not aiming at getting the data
from table 1 and 2 edited into 1 column. I want the relations like they are
in table 3 i.e. two rows but instead of outputting two rows of IDs I want to
output two rows containing the corresponding data from table 1 and 2.
Just like you did actually, but just in two rows instead of one, would that
be easy to fix?
I'll save your concatenating code though, I have another task where I think
I can put it to use
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:O98n5JQJHHA.4848@.TK2MSFTNGP04.phx.gbl...
> lb
> Try this
>
> create table #t1(id int, FName varchar(10))
> create table #t2(id varchar(1), SName varchar(10))
> create table #t3(id int,id2 varchar(1))
>
> insert into #t1 values(1,'John')
> insert into #t1 values(2,'Martin')
> insert into #t1 values(3,'Jenny')
>
> insert into #t2 values('A','King')
> insert into #t2 values('B','Brown')
> insert into #t2 values('C','Andersson')
> insert into #t3 values(1,'C')
> insert into #t3 values(2,'A')
> insert into #t3 values(2,'C')
> insert into #t3 values(3,'A')
> insert into #t3 values(3,'B')
>
> select distinct f.fname + ' ' + l.sname
> from #t3 t4 inner join
> (select t1.id,t1.fname
> from #t3 tt3
> inner join #t1 t1 on t1.id=tt3.id
> ) f on f.id=t4.id
> inner join
> (select t2.id,t2.sname
> from #t3 tt3
> inner join #t2 t2 on t2.id=tt3.id2
> )l on t4.id2=l.id
>
> VT
>
> "Ib Schrader" <ibschrader@.gmail.com> wrote in message
> news:On%23vszPJHHA.2140@.TK2MSFTNGP03.phx.gbl...
>