如何在多个表上使用多个条件连接

Question

我正在编写一个查询，能够检查同一用户的多行。如果同一用户的记录组合没有提供我需要的信息(请记住，它是用户拥有的所有记录的组合，而不是单个记录的组合)，则认为该用户已通过。

例如:有两个表。

一个是"user“，保存用户的个人信息：

id client_id first_name last_name date_of_birth ssn address

另一种是“筛选”，保存用户的体检信息：

id user_id date cholesterol ldl hemoglobin triglycerides mcv glucose 
mchc ha1c plateletcount.

一个用户在用户表中只能有一条记录，但在筛选表中可以有多条记录。我想要做的是检查属于同一用户的用户的多个筛选记录，看看这些记录的组合是否提供了我需要的必要信息。如果不是，则选择该用户。例如，必要的信息包括胆固醇、低密度脂蛋白、甘油三酯、葡萄糖或。如果用户有两条筛选记录，一条记录提供cholesterol(NOT NULL)，另一条记录提供triglycerides(NOT NULL)、glucose(NOT NULL)和ha1c(NOT NULL)。他被选中是因为缺少低密度脂蛋白。

我如何编写能够做到这一点的查询呢？我尝试过内部连接，但似乎不起作用。这里有一些要求。对于胆固醇、低密度脂蛋白和甘油三酯，只要其中一种缺失，就应该选择使用者。但是对于葡萄糖和ha1c，只有当两者都缺失时，用户才会被选中。

我尝试过的一个查询是这样的：

   SELECT users.id AS user_id, users.first_name, users.last_name, clients.name AS client, 
       users.social_security_number AS ssn, users.hiredate, hra.id AS hra_id, hra.date             AS hra_date, hra.maileddate AS hra_maileddate, 
            screening.id AS screening_id, screening.date AS screening_date,         screening.maileddate AS screening_maileddate
        FROM users 
        INNER JOIN clients
        ON(
           users.client_id = clients.id
           )
        INNER JOIN hra
        ON(
           users.id = hra.user_id
           )
        LEFT JOIN screening
        ON(
           users.id = screening.user_id
          )
        WHERE users.client_id = '1879'      
        AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
        AND hra.maileddate IS NOT NULL 
        AND screening.date BETWEEN '2011-05-15' AND '2011-11-15'
        AND screening.maileddate IS NULL    
        AND screening.cholesterol IS NOT NULL
        AND screening.ldl IS NOT NULL
        AND screening.triglycerides IS NOT NULL
        AND (screening.glucose IS NOT NULL OR screening.ha1c IS NOT NULL)
        GROUP BY users.id

正确的查询是什么？

Answer 1

只需将检查实验室的条件从WHERE移至ON即可

    ......
    LEFT JOIN labs
    ON(
       users.id = labs.user_id
       AND labs.date BETWEEN '2011-05-15' AND '2011-11-15'
       AND labs.maileddate IS NULL    
       AND labs.cholesterol IS NOT NULL
       AND labs.ldl IS NOT NULL
       AND labs.triglycerides IS NOT NULL
       AND (labs.glucose IS NOT NULL OR labs.ha1c IS NOT NULL)
      )
    WHERE users.client_id = '1879'      
    AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
    AND hra.maileddate IS NOT NULL 

如果您希望查询返回的用户在hra中没有条目，那么对hra执行相同的操作(别忘了，它应该是LEFT JOIN)

Answer 2

看起来您想要的是OR而不是AND。此外，如果您希望在缺少某些内容时选择它们，请选择"IS NULL"，not on "IS NOT NULL“。

您可以从查找具有HRA的用户开始：

WHERE users.client_id = '1879'      
    AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'

//然后看起来你想要这样的东西：

AND(  labs.cholesterol IS NULL
OR  labs.ldl IS NULL
OR labs.triglycerides IS NULL
OR (labs.glucose IS NULL AND labs.ha1c IS NULL))

Answer 3

通过检查WHERE子句中的NOT NULL，您违背了LEFT JOIN的目的，本质上是使其成为内JOIN...

SELECT users.id AS user_id, users.first_name, users.last_name, clients.name AS client, 
       users.social_security_number AS ssn, users.hiredate, hra.id AS hra_id, 
       hra.date AS hra_date, hra.maileddate AS hra_maileddate, 
       screening.id AS screening_id, screening.date AS screening_date,                screening.maileddate AS screening_maileddate
FROM users 
INNER JOIN clients  ON(users.client_id = clients.id)
INNER JOIN hra ON(users.id = hra.user_id)
-- This says, find matching records in labs, and if there aren't any, then bring
-- back the fields anyway, filled will NULL's
LEFT JOIN labs ON(users.id = labs.user_id)
WHERE users.client_id = '1879'      
AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
AND hra.maileddate IS NOT NULL 
-- These in the WHERE clause defeats the purpose of the LEFT JOIN
-- Move them into the ON condition of the LEFT 
            AND labs.date BETWEEN '2011-05-15' AND '2011-11-15'
            AND labs.maileddate IS NULL    
            AND labs.cholesterol IS NOT NULL
            AND labs.ldl IS NOT NULL
            AND labs.triglycerides IS NOT NULL
            AND (labs.glucose IS NOT NULL OR labs.ha1c IS NOT NULL)
GROUP BY users.id

当LEFT JOIN没有找到匹配的记录时，它从一行取回字段，但用NULL填充它们的值。