求一SQL语句！

[liuhx]

现在tableA(cNo,cType),表内容('aaa','test1)('bbb','test2)
tableB(cNo,cItmNo,cName),表数据（'aaa','1','aaa1'),('aaa','2','aaa2')('aaa','3','aaa3)
('bbb','1','bbb1'),('bbb','2','bbb2'),('bbb','3','bbb3')
和
tableX(cNo,cType),表内容('aaa','test1)('bbb','test2)

tableY(cNo,cItmNo,cName)数据（'aaa','1','aaa1'),('aaa','2','aaa2')('aaa','3','aaa3)
('bbb','1','bbb1'),('bbb','2','bbb2'）
现在我要写一语句实现tableY中没有的这一笔记录('bbb','3','bbb3')

[xuam]

select * from tableY where cNo='bbb' and cItmNo='3' and cName='bbb3'

[Dream_1986]

好像没看明白。。

[huanghe63]

select * from tableb b
where not exists(select * from tabley y where b.cno=y.cno and b.citmno=y.citmno and c.cname=y.cname )

是找出了tableY在tableb中没有的这一笔记录('bbb','3','bbb3')



现在我要写一语句实现tableY中没有的这一笔记录('bbb','3','bbb3')???????

[liuhx]

tableY没有的记录而table有的记录，显示出tablex中的对应('bbb','test2)

[siegebaoniu]

select * from tablex
inner join
(select * from tableB
except
select * from tableY)a
where tablex.cNo=a.cNo

[siegebaoniu]

这个：select tablex.* from tablex........

[lyflcear]

个人思路，用group 再比较记录数量展示数据

[dawugui]

现在我要写一语句实现tableY中没有的这一笔记录('bbb','3','bbb3')

select m.* from tableB m where not exists(select 1 from tableY n where n.cNo = m.cNo and n.cItmNo = m.cItmNo )

其他两表有什么用？

[dawugui]

create table tableA(cNo varchar(10),cType varchar(10))
insert into tablea values('aaa','test1')
insert into tablea values('bbb','test2')
create table tableB(cNo varchar(10),cItmNo varchar(10),cName varchar(10))
insert into tableb values('aaa','1','aaa1')
insert into tableb values('aaa','2','aaa2')
insert into tableb values('aaa','3','aaa3')
insert into tableb values('bbb','1','bbb1')
insert into tableb values('bbb','2','bbb2')
insert into tableb values('bbb','3','bbb3')
create table tableX(cNo varchar(10),cType varchar(10))
insert into tablex values('aaa','test1')
insert into tablex values('bbb','test2')
create table tableY(cNo varchar(10),cItmNo varchar(10),cName varchar(10))
insert into tabley values('aaa','1','aaa1')
insert into tabley values('aaa','2','aaa2')
insert into tabley values('aaa','3','aaa3')
insert into tabley values('bbb','1','bbb1')
insert into tabley values('bbb','2','bbb2')
go

--tablea , tablex的用处在哪里？
select m.* from tableB m where not exists(select 1 from tableY n where n.cNo = m.cNo and n.cItmNo = m.cItmNo )
/*
cNo        cItmNo     cName      
---------- ---------- ---------- 
bbb        3          bbb3

（所影响的行数为 1 行）
*/

--四个表都用上？
select m.* from tableB m where m.cNo in (select cNo from tablea) and 
not exists(select 1 from tableY n where n.cNo in (select cNo from tablex) and n.cNo = m.cNo and n.cItmNo = m.cItmNo )
/*
cNo        cItmNo     cName      
---------- ---------- ---------- 
bbb        3          bbb3

（所影响的行数为 1 行）
*/

drop table tablea , tableb ,tablex , tabley