Query issue with IN Clause

[saadi06]

Hi,
I have a table in which there are two columns. One column is id and the other is value. When I try to retrieve the data that is not empty it gives me unwanted error. I have to apply join to some tables as well. My query is something like this SELECT count(DISTINCT ID) FROM Tbl inner join tbl1 on tbl.id=tbl1.id WHERE id IN(1,2,3) and value!=''.But it also resturns the values that are empty. Can anyone help. Thanks in advance.

[minitauros]

Do you have any NULL values in your database? Because then you would have to append "AND value IS NOT NULL" to your query :).

[diafol]

SELECT count(DISTINCT ID) FROM Tbl inner join tbl1 on tbl.id=tbl1.id WHERE id IN(1,2,3) and value!=''

You don't specify the tables in the where clause. Or in the count part. I don't think the count stuff will work count(DISTINCT ID)

[saadi06]

Thanks for replying. Diafol I just gave an example for the query. The query is working when I have one value in the IN(1) portion. But when I add multiple ids and put the condition that those ids that are selected should not have an empty value like select name from id 1,2,3 whose value is not empty. It will give me the values of those ids as well that are empty.

[diafol]

I'm not sure about the NULL values, as !='' should also filter these out.

Could you post your actual query?

[TomH.PG]

NULL values and non-empty strings will be included by != ""

You should probably add an additional test for that.

SELECT count(DISTINCT id) FROM tbl INNER JOIN tbl1 ON tbl.id = tbl1.id WHERE id IN(1,2,3) AND value != '' AND value IS NOT NULL;

[saadi06]

This is my original query and it is not working

SELECT e.employee AS `Employee Id`,
CONCAT(ep.firstname,' ',ep.lastname) AS `Employee Name`,
IF(ep.sex='M','Male','Female') AS sex,
DATE_FORMAT(ep.birthdate,'%m/%d/%Y')AS birthdate
FROM tbl e  
INNER JOIN history_data AS phd ON e.nr = phd.nr   
INNER JOIN employee ep ON e.employee = ep.employee
WHERE phd.history_value IN (2,3,4,6,63,926)  
AND e.created_date BETWEEN @fromDate AND @toDate AND (phd.history_value!='') 
GROUP BY e.employee

[saadi06]

I have a table named as history_data. The data for the history table is like this 

Table history_data
id    history_date history_nr  history_value     
1     2012-01-01    2           Ab
2     2012-01-01    3           AB
3     2012-01-01    4           
4     2012-01-01    926         CD

I am running a sql query to get those values whose history_nr is IN(2,3,4,926) and whose values is not empty. I have to apply join to other table as mentioned in the above query but the data is not displaying correctly. This query is mentioned in the above post.
The problem with this query is that it will display me those values that are empty as well. How can I fix this problem.
Thanks in advance.