CODEWITHSUNDEEP
mysqlsql
John Baum
John Baum

Reputation: 3331

Query to return rows according to column conditon

I am trying to write a query where given a table of users with a column name (for ex) "Likes", i want to select all those users that have records with both a 0 and a 1 for the "Likes" column. The schema looks something like this:

id  Name  Likes
0   Tom   1
1   Alice 0
2   Tom   0

The query should return rows with id = 0 and id = 2 since Tom has both a 1 and a 0 for the Like column. What is the simplest/most efficient query to accomplish this sort of behavior?

Upvotes: 1

Views: 37

Answers (2)

FrankPl
FrankPl

Reputation: 13315

Assuming your table is called tab, you can use:

SELECT *
  FROM tab a
 WHERE (SELECT count(distinct b.likes)
         FROM tab b
        WHERE a.name = b.name
          AND b.likes in(0, 1)
       ) = 2

This can easily be extended to any number of distinct likes that are required. Just enumerate them in the IN clause, and compare the subselect to the number of values.

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39437

If Likes can be only 1 or 0 in your table and nothing else, you can do this.

select distinct t1.Name From tableName t1
join tableName t2 on t1.name = t2.name and t1.Likes = 1 - t2.Likes

If that is not the case, just do this:

select distinct t1.Name From tableName t1
join tableName t2 on t1.name = t2.name and t1.Likes = 1 and t2.Likes = 0

Another possible solution is this one.

select a.* from
tableName a 
JOIN 
(
    select b.Name, count(distinct b.likes) as Likes
    from tableName b
    group by b.Name
    having count(distinct b.likes) > 1
) a1 on a.Name = a1.Name

Upvotes: 2

Related Questions