CODEWITHSUNDEEP
mysqlsql
savionok
savionok

Reputation: 485

Specific SQL with group by does not work correctly

I have this table in my mysql database

+-----+----------+------+----------+
| id  | group_id | a_id | status   |
+-----+----------+------+----------+
|   2 |      144 |  266 | active   |
|   7 |      160 |  105 | inactive |
|   8 |        0 |  262 | inactive |
|  11 |      120 |  260 | inactive |
|  12 |      120 |  260 | inactive |
|  13 |      121 |  260 | active   |
|  14 |      122 |  258 | active   |
|  14 |      122 |  258 | inactive |
|  16 |      130 |  210 | active   |
|  17 |      130 |  210 | active   |
+-----+----------+------+----------+

I need to select a_id in such a way that all statuses in the same group (group_id) must be inactive and different from 0. What i want to obtain is actually an array of ids (105,260), from this table.

I came to this sql, but apparently it is not working correctly:

select a_id from tab_name where group_id<>0 and group_id in 
  (select group_id from tab_name where status="inactive" 
    group by group_id having status="inactive")

Upvotes: 4

Views: 183

Answers (5)

echo_Me
echo_Me

Reputation: 37243

you could use it easy like this

   select a_id from tab_name where group_id<>0 and status="inactive" 
   group by group_id

update:

   select a_id from tab_name where group_id<>0 and status="active" 
  and a_id not in (select a_id from tab_name where status ='inactive')
   group by group_id

demo

Upvotes: 1

fthiella
fthiella

Reputation: 49089

SELECT   DISTINCT a_id
FROM     yourtable
WHERE    group_id!=0
GROUP BY a_id, group_id
HAVING   SUM(status='inactive')=COUNT(*);

Please see fiddle here.

Upvotes: 4

Stokedout
Stokedout

Reputation: 11055

I think you probably need to adjust your query slightly and use DISTINCT instead of GROUP BY

Try this:

select a_id from tab_name where group_id<>0 and group_id in 
(select distinct group_id from tab_name where status="inactive")

Upvotes: 0

Jim
Jim

Reputation: 22656

The problem is that, for your query, if there is a single inactive row then that group will be returned. Instead you can try a subquery that checks that there is no item in the group that is inactive or has a group_id of 0:

SELECT t1.a_id FROM tab_name t1 
WHERE NOT EXISTS(
               SELECT * FROM tab_name t2 
              WHERE t1.group_id = t2.group_id 
                 AND (t2.status = "inactive" OR t2.group_id = 0))

Upvotes: 0

Pawan
Pawan

Reputation: 1075

try following

select a_id from tab_name where group_id<>0 and status="inactive"

Upvotes: 0

Related Questions