这是一本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
-----------------------------说的不是很清楚,大概就是这样了,希望大家指点下我这菜鸟...
有三张表格,分别是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
-----------------------------说的不是很清楚,大概就是这样了,希望大家指点下我这菜鸟...
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 ?
if object_id('[memberdetails]') is not null drop table [memberdetails]
go
create table [memberdetails]([memberid] int,[firstname] varchar(2),[lastname] varchar(4))
insert [memberdetails]
select 1,'张','效益' union all
select 2,'里','说客' union all
select 3,'我','我们' union all
select 3,'是','速度'if object_id('[category]') is not null drop table [category]
go
create table [category]([categoryid] int,[category] varchar(5))
insert [category]
select 1,'科幻' union all
select 2,'动作' union all
select 3,'APIAN'
if object_id('[favcategory]') is not null drop table [favcategory]
go
create table [favcategory]([categoryid] int,[memberid] int)
insert [favcategory]
select 1,1 union all
select 1,2 union all
select 2,3 union all
select 2,4 union all
select 3,4SELECT B.category, A.firstname, A.lastname
FROM memberdetails A INNER JOIN favcategory C ON A.memberid = C.memberid
INNER JOIN category B ON C.categoryid = B.categoryid
WHERE B.category = '动作'
DROP TABLE memberdetails
DROP TABLE category
DROP TABLE favcategory
--测试结果:
/*
category firstname lastname
-------- --------- --------
动作 我 我们
动作 是 速度(所影响的行数为 2 行)*/
from
favcategory a inner join favcategory b
on a.categoryid=b.categoryid and a.memberid>b.memberid
inner join category c
on c.categoryid=a.categoryid
category,a.firstname,a.lastname ,b.firstname,b.lastname
from
favcategory a,favcategory b,category c
where
a.categoryid=b.categoryid
and
a.memberid>b.memberid
and
c.categoryid=a.categoryid
这个是比较的,格式...
firstname,lastname,firstname2,firstname2,category
两个名字喜欢相同的类型,但是名字不能相同.
但是有类型,有名字,可是选择出来的名字是相同的,无法变成不同的.
有类型,有了ID却无法和memberdetails表里的英文名字连接上,这就是我如今的困惑了...
SELECT category,c.firstname,c.lastname,d.firstname,d.lastname
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)
inner join memberdetails as c
on c.memberid=a.memberid)
inner join memberdetails as d
on d.memberid=b.memberid
order by category谢谢各位大大的热心帮忙...