CODEWITHSUNDEEP
mysqlsqldatabase
rocky
rocky

Reputation: 435

query returning zero results

I have a SQL query:

SELECT count(DISTINCT(email_address))

FROM Mytable

WHERE  TableID = '101'

The above query runs fine.

I want to have results for email addresses, for which TableID has different values like 123,421.

So, when I write the following query, why do I get 0 result returned in MySQLWorkbencch?

SELECT count(DISTINCT(email_address))

    FROM Mytable

    WHERE  TableID = '101'
    AND    TableId = '123'
    AND    TableID = '421'

Upvotes: 0

Views: 566

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

Try with either OR or IN clause. AND returns records when all specified condition is true which is not happen in your case that's why return 0 result or empty result.

SELECT count(DISTINCT(email_address))
    FROM Mytable
    WHERE  TableID = '101'
    OR    TableId = '123'
    OR    TableID = '421';

OR

SELECT count(DISTINCT(email_address))
FROM Mytable
WHERE  TableID IN('101','123','421');

Upvotes: 0

ngrashia
ngrashia

Reputation: 9894

USE IN or OR

  1. By using OR:

    SELECT count(DISTINCT(email_address))
FROM Mytable
WHERE  TableID = '101'
OR  TableId = '123'
OR    TableID = '421'

  2. By using IN:

    SELECT COUNT(DISTINCT(EMAIL_ADDRESS))
FROM MYTABLE
WHERE TABLEID IN ('101','123','421');

Explanation:

  • You are trying to check if a single value matches 3 different unique values, similar to checking if

'Dog is equals to Dog AND Dog is equals to Cat AND Dog is equals to Cow'

Which returns false

  • With Option 1, using OR, it would be like

'Dog is equals to Dog OR Dog is equals to Cat OR Dog is equals to Cow'

Which returns true

  • With Option 2, using IN, it would be like

'Is Dog available in list containing Dog, Cat, Cow'

Which returns true

Upvotes: 2

Robby Cornelissen
Robby Cornelissen

Reputation: 97130

Use OR instead of AND. Using AND, you're requiring a column value to be equal to 3 different constant values. This violates the laws of the universe.

SELECT count(DISTINCT(email_address))
FROM Mytable
WHERE TableID = '101' OR TableId = '123' OR TableID = '421';

Alternatively, you can use the IN clause. In terms of actual execution they're equivalent, but in some cases IN might be easier to read, especially if the list gets longer:

SELECT count(DISTINCT(email_address))
FROM Mytable
WHERE TableID IN ('101', '123', '421');

Upvotes: 5

Thanu
Thanu

Reputation: 2501

Use IN Clause instead.

SELECT count(DISTINCT(email_address))
FROM Mytable
WHERE  TableID IN  ('101','123','421')

Upvotes: 2

Related Questions