How to select the combination of multiple rows(MySQL)

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, group_concat(screening.glucose), group_concat(screening.ha1c)
    FROM users 
    INNER JOIN clients
    ON(
       users.client_id = clients.id
       )
    INNER JOIN hra
    ON(
       users.id = hra.user_id
       AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
       AND开发者_如何学Python hra.maileddate IS NOT NULL 
       )
    LEFT JOIN screening
    ON(
       users.id = screening.user_id
    AND screening.date BETWEEN '2011-05-15' AND '2011-11-15'
    AND screening.maileddate IS NULL   
    AND (screening.cholesterol IS NULL
        OR screening.ldl IS NULL
        OR screening.triglycerides IS NULL)
    AND screening.glucose IS NULL 
    AND screening.ha1c IS NULL
    AND (screening.weight IS NULL
        OR screening.systolic IS NULL
        OR screening.diastolic IS NULL)
    )
    WHERE users.client_id = '1879'      


    GROUP BY users.id

---

I think you have to go with EXISTS() subquery in the WHERE. Something like

SELECT ...
FROM users 
INNER JOIN clients ON(users.client_id = clients.id)
INNER JOIN hra ON(...)
WHERE users.client_id = '1879'
   AND(EXISTS(SELECT 1 FROM screening WHERE(users.id = screening.user_id)AND(...)))

Or NOT EXISTS, depending how you write your criterions.