How to join with many conditions on multiple tables

I am working on writing a query that is able to check multiple rows of the same user. If the combination of the same user's records doesn't provides the required information I need, (remember it is combination of all records a user has, not a single one), the user is considered passed.

For example: There are two tables.

One is "user" which keep user's personal information:

id client_id first_name last_name date_of_birth ssn address

Another one is "screening" which keep users' medical test information:

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

One user can only have one record in user table, but could have multiple records in screening table. What I want to do is check the users' multiple screening records that belongs to the same user to see if the combination of those records provide the necessary information I need. If no, the user is selected. For example, the necessary information includes cholesterol, ldl, triglycerides, glucose or . If a user has two screening records, one record provides cholesterol(NOT NULL) , another one provides triglycerides(NOT NULL), glucose(NOT NULL) and ha1c(NOT NULL). He is selected because ldl is missing.

How do I write the query that is able to do that? I tried inner join, but it seems to not work. There are some requirements here. For cholesterol, ldl and triglycerides, as long as one of them is missing, the user should be selected. But for glucose and ha1c, the user is selected only when both are missing.

One of the query I tried is like this:

   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 scr开发者_开发知识库eening.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

What's the correct query?

Just move conditions that check against labs from WHERE to 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 

Do the same for hra if you need want your query to return users with no entries in hra (don't forget, it should be LEFT JOIN)

By checking for NOT NULL in the WHERE clause, you defeat the purpose of the LEFT JOIN and in essence, make it into an INNER 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

When a LEFT JOIN doesn't find matching records, it brings back the fields from a row, but fills their values with NULL's.

Seems like you want an OR instead of an AND Also, if you want them selected when something is missing, select on "IS NULL", not on "IS NOT NULL"

you start off with finding the user that has an HRA:

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

// Then it seems you want something like this:

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