如何查找二表中不存在的数据room table
---------------------
id roomNo
1 606
2 707
3 808
---------------------custom table
----------------------
id name roomNo
1 lee 707
2 wang 808
----------------------有二个表
room 可以租的房
custom 已经租出的房现在要查询 没有 被租出的房试用左连,只能查出已经出租的房,不能给出 未出租的房。
查询语句:
------------------
SELECT r.roomNO
FROM room AS r
LEFT JOIN CUSTOM AS C
WHERE
r.roomNo=c.roomNo;
------------------输出结果
-----------------
roomNo
707
808
----------------
而不是我想要的606如何做这个查询,看书未得要领,查google很难关键字,请各位援手,谢谢
---------------------
id roomNo
1 606
2 707
3 808
---------------------custom table
----------------------
id name roomNo
1 lee 707
2 wang 808
----------------------有二个表
room 可以租的房
custom 已经租出的房现在要查询 没有 被租出的房试用左连,只能查出已经出租的房,不能给出 未出租的房。
查询语句:
------------------
SELECT r.roomNO
FROM room AS r
LEFT JOIN CUSTOM AS C
WHERE
r.roomNo=c.roomNo;
------------------输出结果
-----------------
roomNo
707
808
----------------
而不是我想要的606如何做这个查询,看书未得要领,查google很难关键字,请各位援手,谢谢
insert into room values(1, 606)
insert into room values(2, 707)
insert into room values(3, 808)
create table custom(id int, name varchar(10), roomNo int)
insert into custom values(1 , 'lee' ,707 )
insert into custom values(2 , 'wang', 808 )
goselect * from room where roomNo not in (select roomNo from custom )drop table room , custom/*
id roomNo
----------- -----------
1 606(所影响的行数为 1 行)
*/
insert into room values(1, 606)
insert into room values(2, 707)
insert into room values(3, 808)
create table custom(id int, name varchar(10), roomNo int)
insert into custom values(1 , 'lee' ,707 )
insert into custom values(2 , 'wang', 808 )
go--方法一
select * from room where roomNo not in (select roomNo from custom )
/*
id roomNo
----------- -----------
1 606(所影响的行数为 1 行)
*/--方法二
select * from room where not exists (select 1 from custom where roomNo = room.roomNo)
/*
id roomNo
----------- -----------
1 606(所影响的行数为 1 行)
*/
drop table room , custom
mysql 用1楼的方法给出了正确的值access2007居然给出的值,是3个都检索出来了。SQL语法虽然不一样的,也不至差这么多。
下午再研究下,我只不过想用access设计个简单的 :(感谢几位的回复,原来做code都要这么晚,谢谢。