I have these 3 tables:
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));
I want to get rows from the report table that meet at least one of the following conditions:
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
First, I create a new report that is publicly accessible:
insert into reports values(null, 'report 1 open to all', 1);
Then another report can only be accessed by user_id = 1:
insert into reports values(null, 'report 2 only for user_id 1', 0); insert into report_users values(null, 2, 1);
Then another report can only be accessed by group_id = 1
insert into reports values(null, 'report 3 only for group_id 1', 0); insert into report_groups values(null, 3, 1);
Now, I have 3 rows: each one is accessible, one row is only accessible by user_id = 1 and another row is only accessible by group_id = 1.
Give me all rows where 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) ;
I get 2 rows. It works.
Give me all rows with 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) ;
I get 2 rows. It works.
but. If report_users or report_groups are empty, I don't get any results. I first run this query:
truncate table report_groups;
When I run the same query as before, I get an empty set. Why? Actually, it doesn't look like there's any difference in the user_id and group_id I'm sending. I always get 0 rows.
To me it seems like just because one of the two tables is empty I'm not getting any results. Is there something wrong with the query itself?
What do you do with this line of code:
is an (old style)
CROSS JOIN
of 3 tables, which means that if one of the tables is empty, the result will also be empty.Instead use
EXISTS
: