我有三张桌子:
create table reports(id int not null AUTO_INCREMENT,name varchar(255)not null,public_access tinyint not null,primary key (id));
create table report_users(id int not null AUTO_INCREMENT,report_id int not null,user_id int not null,primary key (id),foreign key (report_id) references reports(id));
create table report_groups(id int not null AUTO_INCREMENT,report_id int not null,group_id int not null,primary key (id),foreign key (report_id) references reports(id));
我要从至少满足以下条件之一的Reports-TABLE中获取行:
1 - The field public_access is true
2 - The report is in the related table report_users with in parameter user_id
3 - The report is in the related table report_groups with in parameter group_id
首先,我创建一个具有公共访问权限的新报告:
insert into reports values(null, 'report 1 open to all', 1);
则只能由user_id=1访问的另一报表:
insert into reports values(null, 'report 2 only for user_id 1', 0);
insert into report_users values(null, 2, 1);
则只能由group_id=1访问的另一报表
insert into reports values(null, 'report 3 only for group_id 1', 0);
insert into report_groups values(null, 3, 1);
现在,我有3行:一行可供所有人访问,一行仅供user_id=1访问,另一行仅供group_id=1访问.
给我user_id=1的所有行:
select reports.*
from reports, report_users,report_groups
where
reports.public_access = 1
or
(report_users.report_id = reports.id and report_users.user_id = 1)
or
(report_groups.report_id = reports.id and report_groups.group_id = 5)
;
我有两排.它起作用了.
给我group_id=1的所有行:
select reports.*
from reports, report_users,report_groups
where
reports.public_access = 1
or
(report_users.report_id = reports.id and report_users.user_id = 4)
or
(report_groups.report_id = reports.id and report_groups.group_id = 1)
;
我有两排.它起作用了.
但.如果REPORT_USERS或REPORT_GROUPS为空,则不会得到任何结果.我首先运行以下查询:
truncate table report_groups;
当我像以前一样运行相同的查询时,我得到一个空集.为什么?实际上,我发送的USER_ID和GROUP_ID似乎没有任何区别.我将始终得到0行.
在我看来,只是因为两张桌子中的一张是空的,我就得不到任何结果. 是查询本身有问题吗?