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?

from reports, report_users,report_groups

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 = ?);