只有当子表中的记录与另一个子表中的记录匹配时，MySQL才从父表中选择行

Question

我有两组父/子表：

1. 转介/ modalities_match_referrals
2. 地点/ modalities_match_providers

另一种表格模式是我们通常处理的所有模式的清单。

对于任何转诊，我需要找到一个供应商列表(地点表)，可以执行每一种模式所需的转诊。例如，如果转诊需要核磁共振成像和关节造影，则清单应限于既能进行MRI检查又能进行关节造影的人，即使这种提供者提供的是这种转诊所需的其他模式。

我的问题：是否有一个单独的查询(带有子查询)会导致一个列表，其中只有那些能够通过modalities_match_referrals表执行链接到引用的所有模式的位置/提供者？

我的问题是，当一个转诊需要一个以上的模式时，就会出现过度包容的问题。我的查询将生成一个提供程序列表，该列表至少可以执行其中的一个模式，并且包括无法执行所有模式的提供程序。

以下是子表/匹配表：

CREATE TABLE modalities_match_referrals (
id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
,referral_id INT NOT NULL
,modality_id INT NOT NULL
,on_off TINYINT DEFAULT 0
,INDEX(referral_id)
,INDEX(modality_id)
,FOREIGN KEY(referral_id) REFERENCES referrals(id)
,FOREIGN KEY(modality_id) REFERENCES modalities(id)
);

CREATE TABLE modalities_match_providers (
id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
,location_id INT NOT NULL
,modality_id INT NOT NULL
,on_off TINYINT DEFAULT 0
,INDEX(location_id)
,INDEX(modality_id)
,FOREIGN KEY(location_id) REFERENCES locations(id)
,FOREIGN KEY(modality_id) REFERENCES modalities(id)
);

我到目前为止尝试过的有一个长的生产查询，它太长了，不能在这里包含，但是我从这个较短的查询和子查询中得到了相同的提供者列表：

SELECT locations.id AS 'Location ID'
,locations.name AS 'Provider'
FROM locations
JOIN (
    SELECT p.location_id
    FROM modalities_match_providers AS p
    RIGHT JOIN (
    SELECT modality_id
        FROM modalities_match_referrals
        WHERE on_off=1
        AND referral_id=9660        
    ) AS r ON r.modality_id = p.modality_id
    WHERE on_off=1
) AS l ON l.location_id=locations.id

WHERE locations.recycle=0
AND locations.locationstateid=1
GROUP BY locations.id
ORDER BY locations.name
LIMIT 10;

结果包括可以进行磁共振成像和/或关节造影的提供者，而我所要寻找的只是那些能够同时进行这两种检查的提供者。

在过去，在引用表和位置表中作为列处理的模式。sql要简单得多，但当引入新的模式时，该系统的问题是在这些表中有列(这比您想象的更多--打开MRI与站立MRI，等等)。

我还在考虑创建一个PHP类，以便在会将列表呈现到网页上的while循环中调用。该类将对modalities_match_referrals和modalities_match_providers进行比较，并返回true或false。根据返回值，将显示或不显示提供程序。缺点是复杂的，我会关注页面的性能。

谢谢您能提供的任何帮助。

Answer 1

交叉加入转诊模式与地点，以获得最佳的结果。然后，外部加入供应商的模式，看看实际覆盖了多少。使用GROUP BY和HAVING获取没有模式的引用/位置对。

select
  r.referral_id,
  l.id as location_id
from modalities_match_referrals r
cross join locations l
left outer join modalities_match_providers p
  on  p.referral_id = r.referral_id
  and p.modality_id = r.modality_id
  and p.location_id = l.id
group by r.referral_id, l.id
having count(*) = count(p.id)
order by r.referral_id, l.id;

这将为您提供所有可以执行其所有模式的提供者的推荐。