SQL:用WHERE子句连接表

Question

我试图连接两个表，共享相同的个人ID (key)。第一个表(a)是一个"宽"表，有许多变量，包括key和age;第二个表(b)是一个"长"表，只包括变量key、diagnosis_number和diagnosis，其中每个个体可以有多个diagnosis的值。

我想选择key,age列和个人的主要诊断，其中:

• diagnosis="a"b",或"c"当diagnosis_number= 1['初步诊断']
• ANDdiagnosis= "y"diagnosis_number= 2:20

我试过:

SELECT main.key, main.age, diag.diagnosis
FROM a as main
INNER JOIN

(
SELECT prim.key, prim.diagnosis
FROM
(SELECT DISTICT key, diagnosis
FROM b
WHERE diagnosis IN ('a', 'b', 'c')
AND diagnosis_number = 1) as prim
INNER JOIN
(SELECT DISTICT key, diagnosis
FROM b
WHERE diagnosis = 'y'
AND diagnosis_number BETWEEN 2 AND 20) as sec
ON prim.key = sec.key) as diag
ON main.key = diag.key

Answer 1

认为这可以在没有子查询/select的情况下解决:

SQL Server

SELECT a.key, a.age, b.diagnosis
FROM a
INNER JOIN b
ON b.key = a.key
WHERE b.diagnosis IN ('a', 'b', 'c')
AND b.diagnosis_code = 1
AND a.key IN (SELECT b1.key 
FROM b AS b1
WHERE b1.diagnosis = 'y'
AND b1.diagnosis_code BETWEEN 2 AND 20)