How to query 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. It shows users that should not be shown. Please help me have a look and tell me what is wrong:

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, 
        sall.id AS screening_id, sall.date AS screening_date, sall.maileddate AS     screening_maileddate
    FROM users 开发者_开发问答
    INNER JOIN clients
    ON(
       users.client_id = clients.id
       AND users.client_id = '1879'
       )
    INNER JOIN hra
    ON(
       users.id = hra.user_id
       AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
       AND hra.maileddate IS NOT NULL 
       )
     INNER JOIN screening sall
     ON(
     sall.user_id = users.id 
     )
      INNER JOIN screening s1
      ON(
      s1.user_id = users.id

      AND s1.date BETWEEN '2011-05-15' AND '2011-11-15'
    ) 
    INNER JOIN screening s2
    ON(
       s2.user_id = users.id

      AND s2.date BETWEEN '2011-05-15' AND '2011-11-15'
    ) 
    INNER JOIN screening s3
    ON(
      s3.user_id = users.id

      AND s3.date BETWEEN '2011-05-15' AND '2011-11-15'
    )
    INNER JOIN screening s4 
    ON(
       s4.user_id = users.id

      AND s4.date BETWEEN '2011-05-15' AND '2011-11-15'
    )
    INNER JOIN screening s5
    ON(
       s5.user_id = users.id

      AND s5.date BETWEEN '2011-05-15' AND '2011-11-15'
  )
    INNER JOIN screening s6
    ON(
      s6.user_id = users.id

      AND s6.date BETWEEN '2011-05-15' AND '2011-11-15'
  )

    WHERE ((s1.cholesterol IS NULL
    OR s2.ldl IS NULL
    OR s3.triglycerides IS NULL)
    OR (s4.glucose IS NULL
    AND s5.ha1c IS NULL))
    AND s6.maileddate IS NULL
    GROUP BY users.id

You don't want inner join, you want left join. Inner join suppresses the result row when any of the join conditions fail, whereas left join lets the result row appear with null items.

You're very close here.

Do you really have to do the screening with all the inner joins? Consider this (It's not a complete query, but a suggested different screening approach :)

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, 
    sall.id AS screening_id, sall.date AS screening_date, sall.maileddate AS     screening_maileddate
FROM users 
LEFT JOIN clients
ON(
   users.client_id = clients.id
   AND users.client_id = '1879'
)
LEFT JOIN hra
ON(
   users.id = hra.user_id
   AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
   AND hra.maileddate IS NOT NULL 
)
LEFT JOIN screening sall
ON(
   sall.user_id = users.id 
)
WHERE users.id NOT IN
(
  SELECT user_id FROM screening1
  WHERE user_id = users.id AND
        colesterol IS NULL
        date BETWEEN '2011-05-15' AND '2011-11-15'
) 
OR users.id NOT IN
(
  SELECT user_id FROM screening2
  WHERE user_id = user.id AND
        ldl IS NULL
        date BETWEEN '2011-05-15' AND '2011-11-15'
) 
...