访问查询选择

Question

我需要帮助与Access数据库，我不知道该怎么做的查询。 我所拥有的是与存储的疾病名称和ID另一个叫症状存储症状姓名和身份证，并在我相关联的症状给定的疾病另一个叫SymptomsDiseases表疾病的数据库。我想要做的是获得所有具有特定症状的疾病，例如症状为ID 3，4和5的疾病。我应该如何解决这个问题？这些表和字段：

1）疾病

IdDisease

DiseaseName

2）症状

IdSymptom

SymptonName

3）SymptomsDiseases

IdDiseases_fk

IdSymptoms_fk

=>在此表中我将具有例如：

IdDisease_fk || IdSymptoms_fk

6 || 4

6 || 5

6 || 3

6 || 7

6 || 8

4 || 10

4 || 11

4 || 4

4 || 5

4 || 3

Answer 1

这种类型的技术被称为Relational Division

SELECT a.IdDisease, 
     a.DiseaseName 
FROM Diseases AS a 
     INNER JOIN SymptomsDiseases as B 
      ON a.IdDisease = b.IdDiseases_fk 
WHERE b.IdSymptoms_fk IN (3,4,5)    -- <<== list of ID of Symptoms 
GROUP BY a.IdDisease, a.DiseaseName 
HAVING COUNT(*) = 3        -- <<== number of symptoms 

• SQL of Relational Division