How to check combination of records from multiple rows(MySQL)

I am working on writing a query that is able to check multiple rows at the same time. If the combination of the same user's records provides the enough information I need, even ever开发者_运维技巧y single record doesn't provides enough information I need, 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 "lab" which keep users' medical test information: id external_source_id user_id date wbc rbc hemoglobin hematocrit mcv mch mchc rdw plateletcount

One user can only have one record in user table, but could have multiple records in labs table. What I want to do is check the users' multiple lab records that belongs to the same user to see if the combination of those records provide the necessary information I need. If yes, the user is passed, even if any single lab record doesn't provide enough information. For example, the necessary information includes cholesterol, ldl, triglycerides, glucose. If a user has two lab records, one record provides cholesterol(NOT NULL) and ldl(NOT NULL), another one provides triglycerides(NOT NULL), glucose(NOT NULL). He is considered passed.

How do I write the query that is able to do that?

The query I currently have 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 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 
        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

This will select all users in your example

select u.*
from user u
join lab l1 on l1.user_id = u.id and l1.wbc is not null
join lab l2 on l2.user_id = u.id and l2.hemoglobin is not null
join lab l3 on l3.user_id = u.id and l3.plateletcount is not null
-- etc for other fields that need to be not null

This will work even if the same records have more than one desired column, or if the values are spread out across rows.

If you want the lab values too, just select u.*, l1.wbc, l2.hemoglobin, ... etc

If you want the Users who PASS :

You can use a IN with AND clause

Select u.* from user u 
where 
u.user_id in (select user_id from lab where wbc is not null) and
u.user_id in (select user_id from lab where hemoglobin is not null) and
u.user_id in (select user_id from lab where plateletcount is not null);

if you want the users who DONT PASS

You can use a IN with OR clause

Select u.* from user u 
where 
u.user_id in (select user_id from lab where wbc is null) OR
u.user_id in (select user_id from lab where hemoglobin is null) OR
u.user_id in (select user_id from lab where plateletcount is null);

i hope that makes sense :)