Where condition not working with IN Clause

Question

I have a query. In this query I am using a sub query to get the data from the same table with different condition and in the main query I am mentioning the ids that are used to get data in the sub query and putting a condition that the values that are being considered in the main query with the ids should not be empty. If I use a single ID with IN clause in the main query, my query works fine but if I use multiple ids in the main query and add the clause that the values should not be empty, the query does not give me the desired result.Here is my query

SELECT e.id AS `Personal Number`,
e.date AS `Date`,
CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
IF(ep.sex='M','Male','Female') AS sex,
DATE_FORMAT(p.birthdate,'%m/%d/%Y')AS birthdate,
(SELECT `value` FROM employee_data WHERE history=87 AND DATE=e.date) AS `A`,
(SELECT `value` FROM employee_data WHERE history=603 AND DATE=e.date) AS `B`,
(SELECT `value` FROM employee_data WHERE history=82 AND DATE=e.date) AS `C`,
(SELECT `value` FROM employee_data WHERE history=86 AND DATE=e.date) AS `D`
FROM tbl e  
INNER JOIN employee ep ON e.id = ep.id
INNER JOIN tbl2 ap ON ap.date=e.date
INNER JOIN employee_data AS phd ON e.date = phd.date   
WHERE (phd.history IN(82,87,603,86) AND phd.value!='')  AND ap.date BETWEEN '2013-01-01' AND '2013-09-01'AND e.status!='cancelled'. 

I am out of ideas that what to do with this query. Can anyone help. Thanks in advance

Answer 1

Since you fetch values with filter in select clause, do a group by and get max() value in select clause.

max (SELECT `value` FROM employee_data WHERE history=87 AND DATE=e.date) AS `A`,
max (SELECT `value` FROM employee_data WHERE history=603 AND encounter_nr=e.encounter_nr)     AS `B`,
max (SELECT `value` FROM employee_data WHERE history=82 AND encounter_nr=e.encounter_nr) AS `C`,
max  (SELECT `value` FROM employee_data WHERE history=86 AND encounter_nr=e.encounter_nr) AS `D`
FROM

Answer 2

Here is your problem. Your query is producing four rows when you have the where clause. But, I suspect that you are just expecting one row, or at least, just one row per id.

I think this is the query that you want:

SELECT e.id AS `Personal Number`, e.date AS `Date`,
       CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
       IF(ep.sex='M','Male','Female') AS sex,
       DATE_FORMAT(p.birthdate,'%m/%d/%Y') AS birthdate,
       max(case when history = 87 then value end) as A,
       max(case when history = 603 then value end) as B,
       max(case when history = 82 then value end) as C,
       max(case when history = 86 then value end) as D
FROM tbl e INNER JOIN
     employee ep
     ON e.id = ep.id INNER JOIN
     tbl2 ap ON ap.date = e.date INNER JOIN
     employee_data phd
     ON e.date = phd.date   
WHERE phd.history IN (82, 87, 603, 86) AND phd.value <> '' AND
      ap.date BETWEEN '2013-01-01' AND '2013-09-01' AND
      e.status <> 'cancelled'
group by e.id;

This should return one row for each employee.

EDIT:

It occurs to me that you might not want an aggregation. You can follow your original approach by removing the join to employee_data in the outer query:

SELECT e.id AS `Personal Number`, e.date AS `Date`,
       CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
       IF(ep.sex='M','Male','Female') AS sex,
       DATE_FORMAT(p.birthdate,'%m/%d/%Y') AS birthdate,
       (SELECT `value` FROM employee_data WHERE history=87 AND DATE=e.date) AS `A`,
       (SELECT `value` FROM employee_data WHERE history=603 AND DATE=e.date) AS `B`,
       (SELECT `value` FROM employee_data WHERE history=82 AND DATE=e.date) AS `C`,
       (SELECT `value` FROM employee_data WHERE history=86 AND DATE=e.date) AS `D`
FROM tbl e INNER JOIN
     employee ep
     ON e.id = ep.id INNER JOIN
     tbl2 ap ON ap.date = e.date
WHERE ap.date BETWEEN '2013-01-01' AND '2013-09-01'AND e.status <> 'cancelled'
HAVING A <> '' and B <> '' and C <> '' and D <> '';

The having clause is a MySQL trick that lets you refer to column aliases in the from clause. It does not imply aggregation in this case.

You would do this if you had an index employee_data(history, date).