CODEWITHSUNDEEP
mysqlconditional-statements
Djeux
Djeux

Reputation: 125

MySql meeting multiple conditions

I'm having a table, where one ID, can have multiple statuses

| client_id | status_id |
|     1     |     2     |
|     1     |     3     |
|     1     |     5     |
|     2     |     2     |
|     2     |     3     |
|     2     |     6     |

The problem is, to select only those client_id's if they have all the statuses i.e. 2,3,5 (status_id = 2 AND status_id = 3 AND status_id = 5) but mysql doesn't allow that directly.

SELECT * FROM `klient_status` WHERE StatusID = 20 AND StatusID = 40

returns: MySQL returned an empty result set (i.e. zero rows).

Upvotes: 2

Views: 678

Answers (3)

Fenton
Fenton

Reputation: 250922

I think you are trying to obtain a client id where that client id appears in the table twice. So putting the query into English you want:

Get me clients who have a record in this table with a status of 20 and also another record in this table with a status of 40

In which case, you will need to use a subquery:

SELECT client_id 
FROM `klient_status` 
WHERE StatusID = 20 AND client_id IN
(
    SELECT client_id 
    FROM `klient_status` 
    WHERE StatusID = 40
)

Upvotes: 0

Omesh
Omesh

Reputation: 29091

this is because you are using AND clause instead of IN clause to check multiple values in same column:

SELECT *
FROM  klient_status
WHERE status_id  IN(2,3,5)
GROUP BY client_id
HAVING COUNT(*) = 3;

Example @ sqlfiddle

Upvotes: 3

Ghassan Elias
Ghassan Elias

Reputation: 2233

SELECT `client_id`, COUNT(`client_id`) c_count 
FROM `table_name` 
WHERE `status_id` IN (2,3,5) 
GROUP BY `client_id` 
HAVING c_count = 3

Upvotes: 0

Related Questions