Pertanyaan dalam mysql memberikan 0 hasil jika salah satu jadual kosong

Question

Saya ada 3 jadual ni:

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

Saya ingin mendapatkan baris daripada jadual laporan yang memenuhi sekurang-kurangnya satu daripada syarat berikut:

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

Mula-mula, saya mencipta laporan baharu yang boleh diakses secara umum:

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

Kemudian laporan lain hanya boleh diakses oleh user_id = 1:

insert into reports values(null, 'report 2 only for user_id 1', 0);
insert into report_users values(null, 2, 1);

Maka laporan lain hanya boleh diakses oleh group_id=1

insert into reports values(null, 'report 3 only for group_id 1', 0);
insert into report_groups values(null, 3, 1);

Kini, saya mempunyai 3 baris: setiap satu boleh diakses, satu baris hanya boleh diakses oleh user_id = 1 dan baris lain hanya boleh diakses oleh group_id = 1.

Beri saya semua baris di mana 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)
;

Saya mendapat 2 baris. Ianya berfungsi.

Beri saya semua baris dengan 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)
;

Saya mendapat 2 baris. Ianya berfungsi.

Tetapi. Jika report_users atau report_groups kosong, saya tidak mendapat sebarang hasil. Saya mula-mula menjalankan pertanyaan ini:

truncate table report_groups;

Apabila saya menjalankan pertanyaan yang sama seperti sebelumnya, saya mendapat set kosong. kenapa? Sebenarnya, ia tidak kelihatan seperti terdapat sebarang perbezaan dalam user_id dan group_id yang saya hantar. Saya sentiasa mendapat 0 baris.

Bagi saya nampaknya hanya kerana satu daripada dua meja kosong saya tidak mendapat apa-apa keputusan. Adakah terdapat sesuatu yang salah dengan pertanyaan itu sendiri?

Answer 1

Apa yang anda lakukan dengan baris kod ini:

from reports, report_users,report_groups

adalah (gaya lama) CROSS JOIN untuk 3 meja, bermakna jika salah satu meja kosong, hasilnya juga kosong.

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