query in mysql gives 0 result if one of tables is empty

Question

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 reports-table where at least one of the following criteria is true:

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 with public access:

insert into reports values(null, 'report 1 open to all', 1);

Then another report only to 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 only to 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: one accessible by everybody, one only by user_id = 1 and one only for group_id=1.

Give me all the rows for 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 the rows for 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 get no result. I run this query first:

truncate table report_groups;

When I run the same query as before, I get an empty set. Why? Actually it looks like it does not make any difference the user_id and group_id I send in. I will always get 0 rows.

To me it looks like just because one of the two tables are empty, I do not get any result. Is it something wrong with the query itself?

Answer 1

What you are doing with this line:

from reports, report_users,report_groups

is a (old style) CROSS JOIN of the 3 tables, which means that if one of the tables is empty then the result is also empty.

Instead use EXISTS:

select r.* 
from reports r
where r.public_access = 1
   or exists (select * from report_users u where u.report_id = r.id and u.user_id = ?)
   or exists (select * from report_groups g where g.report_id = r.id and g.group_id = ?);