Help with MySQL query to select only records with specific value in column

Question

I have following table:

+--------+-----------+--------+
|   ID   |  Title    |  Stat  |
+--------+-----------+--------+
|    1   |  title a  |    1   |
+--------+-----------+--------+
|    1   |  title b  |    0   |
+--------+-----------+--------+
|    2   |  title c  |    1   |
+--------+-----------+--------+
|    2   |  title d  |    1   |
+--------+-----------+--------+
|    3   |  title e  |    0   |
+--------+-----------+--------+
|    3   |  title f  |    1   |
+--------+-----------+--------+
|    3   |  title g  |    1   |
+--------+-----------+--------+

I need to construct a query, where as a result only those records would be included where ALL values in column Stat meet certain condition.

For above table, how to formulate a query that would basically mean: show me records for only those ID's that have all the values in column Stat = 1 and would as a result produce following table?

+--------+-----------+--------+
|    2   |  title c  |    1   |
+--------+-----------+--------+
|    2   |  title d  |    1   |
+--------+-----------+--------+

Thanks.

@middaparka: Nobody said that ID is the primary key here; maybe it's a foreign key. Having said that, I'm also curious how one can end up with a non-unique column called ID (assuming that it is aptly named). @Ales: you might get a better answer if you tell us more about the application / database schema. — Martijn
– Martijn, Commented Jan 28, 2010 at 13:19
It is actually named differently (id_member) and it is a foreign key. Basically I have table with members and another table with books those members have loaned. Loans table has key: (id_member, id_book). Every book can be marked as returned (1) or not returned (0). So I was asking for a query to find all members that have no loaned books. — Aleš
– Aleš, Commented Jan 28, 2010 at 14:14

Martijn · Accepted Answer · 2010-01-28 13:15:50Z

3

Something like this?

SELECT
  *
FROM table t
LEFT JOIN (
  SELECT
    MIN(Stat) AS Stat,
    ID
  FROM table
  GROUP BY ID
) minstat ON t.ID = minstat.ID
WHERE minstat.Stat = 1;

Basically, the subquery computes the minimum stat per ID, whereas the outer query only selects the rows with the minimal stat equal to 1. If all rows need to satisfy a certain condition, insert a boolean expression in stead of Stat in the minimum.

Not sure if this is possible without a subquery.

answered Jan 28, 2010 at 13:15

Martijn

5,6915 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martijn Over a year ago

Welcome. Still curious though, might be that you can improve the database schema to avoid this kind of strange queries

user222162 · Accepted Answer · 2010-01-28 13:39:48Z

0

This also works,

SELECT * FROM tablename t1  
WHERE (
        SELECT COUNT(*) FROM tablename t2 
        WHERE t1.ID = t2.ID AND t2.STAT = 0
      ) = 0

this query checks, is where a STAT with value 0 in records with same ID.

answered Jan 28, 2010 at 13:39

user222162

1 Comment

Aleš Over a year ago

Yep, this one works as well, only, when you have lots of records, Martijn's solution seems to be much faster. Thanks anyway.

Alex Monthy · Accepted Answer · 2010-01-28 14:53:46Z

0

How about this:

select * from t where not (id in (select id from t where stat=0));

answered Jan 28, 2010 at 14:53

Alex Monthy

1,8871 gold badge14 silver badges25 bronze badges

1 Comment

Alex Monthy Over a year ago

Ales states that my solution is the slowest. But in my opinion it is the fastest in human comprehension and reproduction time. And since Ales was "..trying to figure this one out for hours..." this may well be a critical factor.

Aleš · Accepted Answer · 2010-01-28 18:53:01Z

0

All 3 suggested solutions above work well, difference is only in the speed of execution. On a table where 3 records are found among approximately 7000 results are:

Martijn's: Showing rows 0 - 2 (3 total, Query took 0.0069 sec)

Alex's: Showing rows 0 - 2 (3 total, Query took 0.1162 sec)

Pavelasc's: Showing rows 0 - 2 (3 total, Query took 0.6160 sec)

answered Jan 28, 2010 at 18:53

Aleš

791 silver badge9 bronze badges

Collectives™ on Stack Overflow

Help with MySQL query to select only records with specific value in column

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related