CODEWITHSUNDEEP
sqlselectmariadb
Zeroza
Zeroza

Reputation: 23

Select rows only if 2 columns equal in each row

Considering I have the following table:

orders:

id | article_id | order_id | status
39 |     1      |    16    |   2
40 |     1      |    16    |   1
41 |     2      |    16    |   2
42 |     3      |    16    |   2
43 |     2      |    17    |   1
44 |     2      |    17    |   0
45 |     3      |    17    |   1

Now my question:

How can I select the rows of a matching order_id ONLY if ALL status of the single rows are MINIMUM 2?

For example, selecting from the table above, I shouldn't get any results. But as soon as the status from id 40 switches from 1 to 2, the select should give me all rows of the order_id 16. How can that be done with a single SQL-query?

Thanks in advance!

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.order_id = t.order_id and t2.status < 2
                 );

Upvotes: 3

Related Questions