联表查询的问题

[玉琳]

表a
name Id1 Id2
abc 1 2
def 2 1

表b
classid classname
1 aaa
2 bbb

从表a查询name=’abc’的数据,然后根据a表的id1以及id2的值分别等于表b的classid取classname值.id1=1取表b中classid=1的classname的值即为aaa,id2=2取表b中classid=2的classname的值即为bbb, 也就是查询结果是 abc aaa bbb

[君_洛洛]

select a.name,b.classname,c.classname
from a ,b b,b c
where a.id1=b.classid and a.id2=c.classid
and a.name='abc'

[-晴天]

create table ta(name varchar(10),Id1 int,Id2 int)
insert into ta select 'abc',1,2
insert into ta select 'def',2,1
create table tb(classid int,classname varchar(10))
insert into tb select 1,'aaa'
insert into tb select 2,'bbb'
select a.name,b.classname,c.classname from ta a 
inner join tb b on a.id1=b.classid
inner join tb c on a.id2=c.classid
/*
name       classname  classname
---------- ---------- ----------
abc        aaa        bbb
def        bbb        aaa

(2 行受影响)

*/

[BetterMe]

SELECT classname from b where classid in (select id1,id2 from a where name='abc');