CODEWITHSUNDEEP
mysql
Galfau
Galfau

Reputation: 82

MySQL all rows that only match a field value

EDITED

I need to execute a query that only brings me the user_id who have all of his phones confirmed = 2, not just one or two, must be all of them.

My table looks like this:

 id      user_id    phone        confirmed
 ------- --------   -----------  ---
 1       1          3051234561   1
 1       1          3051234562   0
 1       1          3051234563   2
 1       2          3051234564   2
 1       2          3051234565   2
 1       2          3051234566   2
 1       3          3051234567   0
 1       3          3051234568   1
 1       4          3051234569   1

In this case must return user_id 2

I was trying to do it with GROUP HAVING but Iam not getting the result I need. Example:

SELECT
*
FROM
    phones
GROUP BY 
    user_id
HAVING 
    MAX(confirmed) = 2

Upvotes: 1

Views: 1408

Answers (7)

Faishal
Faishal

Reputation: 1493

Try this 

SELECT distinct user_id FROM phones GROUP BY user_id HAVING sum(confirmed) = 2 * count(confirmed)

this will give all user_id who haven't any confirmed phone

Upvotes: 2

Devart
Devart

Reputation: 121912

Try this query -

SELECT
 user_id
FROM
  phones
GROUP BY 
  user_id
HAVING 
  COUNT(IF(confirmed = 2, 1, NULL)) = COUNT(*)

Output:

+---------+
| user_id |
+---------+
|       2 |
+---------+

Upvotes: 1

İsmet Alkan
İsmet Alkan

Reputation: 5447

SELECT distinct user_id FROM phones GROUP BY user_id HAVING sum(confirmed) = 0

You wrote:

SELECT
*
FROM
    phones
GROUP BY 
    user_id
HAVING 
    MAX(confirmed) = 2

Now you need all the confirmed to be 0, getting their sum must be zero, and you want not all attributes but user_id:

SELECT
    distinct(user_id)
FROM
    phones
GROUP BY 
    user_id
HAVING 
    SUM(confirmed) = 0

Upvotes: 0

sAnS
sAnS

Reputation: 1163

You may try like this

SELECT user_id FROM phones WHERE confirmed=0 GROUP BY user_id;

Upvotes: 0

Shailesh
Shailesh

Reputation: 990

try this one

SELECT *
FROM phones
Where confirmed= 0
GROUP BY user_id

Upvotes: 0

Ammar Hayder Khan
Ammar Hayder Khan

Reputation: 1335

Try the following one....

   "SELECT * FROM phones WHERE confirmed='0' GROUP BY user_id"

Upvotes: 0

user1781710
user1781710

Reputation:

You need a WHERE condition before your GROUP BY

Upvotes: 0

Related Questions