请教各位一个关于左右联合的查询
这是一本SQL入门书的课后作业,目前在用ACCESS练手,本人想了好久都没有办法做出来,在此请各位帮帮忙,谢谢了.
有三张表格,分别是memberdetails,category,favcategory
memberdetails表里主键是memberid,然后后面是名字firstname,lastname
category里包含categoryid,category就是种类ID和种类名称
favcategory里包含categoryid,memberid
-------
如今题目是:举例每对具有相同电影类型爱好的成员.结果必须包含电影类型名和喜爱该类型的成员名和姓.
编了两个左右联合的语句,可是都不对.
--------------------------------------
这条可以全部用文字显示,可是两个列的名字一样,添加WHERE 'a'<>'b'也没有效果.
SELECT category,firstname+lastname as a,firstname+lastname as b
from (favcategory left outer join memberdetails
on memberdetails.memberid=favcategory.memberid)
right outer join category
on category.categoryid=favcategory.categoryid
-------
这条显示了我想要的全部信息,可是名字却只能是ID,在修改下去就是JOIN错误,无法加入memberdetails里的名字...
SELECT category,a.memberid,b.memberid
from (favcategory as a inner join favcategory as b
on a.categoryid=b.categoryid and a.memberid>b.memberid)
inner join category
on category.categoryid=a.categoryid
and category.categoryid=b.categoryid
-----------------------------
说的不是很清楚,大概就是这样了,希望大家指点下我这菜鸟...
[解决办法]
if object_id('[memberdetails]') is not null drop table [memberdetails]gocreate table [memberdetails]([memberid] int,[firstname] varchar(2),[lastname] varchar(4))insert [memberdetails]select 1,'张','效益' union allselect 2,'里','说客' union allselect 3,'我','我们' union allselect 3,'是','速度'if object_id('[category]') is not null drop table [category]gocreate table [category]([categoryid] int,[category] varchar(5))insert [category]select 1,'科幻' union allselect 2,'动作' union allselect 3,'APIAN'if object_id('[favcategory]') is not null drop table [favcategory]gocreate table [favcategory]([categoryid] int,[memberid] int)insert [favcategory]select 1,1 union allselect 1,2 union allselect 2,3 union allselect 2,4 union allselect 3,4SELECT B.category, A.firstname, A.lastnameFROM memberdetails A INNER JOIN favcategory C ON A.memberid = C.memberid INNER JOIN category B ON C.categoryid = B.categoryidWHERE B.category = '动作'DROP TABLE memberdetailsDROP TABLE categoryDROP TABLE favcategory--测试结果:/*category firstname lastname -------- --------- -------- 动作 我 我们动作 是 速度（所影响的行数为 2 行）*/