SQL Query not giving expected output

as below status = A means Active and C means Closed. I want if below this three-row having C then the output will show.

I am considering if all status = ‘C’ means it will be closed otherwise it is active

SELECT * FROM table WHERE status = 'C' AND code = 123 GROUP BY code
// here no output will come as I am expecting because ID 1,2 still having A

how do I do this if all status is the same then it will work, otherwise not give any output.

ID | status    |  code 
1  | A         | 123
2  | A         | 123
3  | C         | 123

Sorry but I really don’t understand your problem. You want all code‘s where all rows are closed?

In that case it’s much easier as you can simply show all code‘s where you cannot find a row which is active.

select code from table t1 left join table t2 on t2.code = t1.code and t2.status = ‘A’ where t2.Id = null

I… think I understand what is being asked…something akin to…

SELECT COUNT(status), code
FROM   table
WHERE  status = "C"
GROUP  BY code
HAVING COUNT(status) >= 3;
  FROM yertable
    BY code
HAVING MIN(status) = 'C'

Nice approach but a little bit too tricky for me, as what happens if there will be another status except A and C or if there will be localized status which have not the same character sequence

i answered the question that you asked

you have now changed the requirements

originally, you said there were statuses A and C

you never suggested that there might be other statuses

so if you would like to restate your problem, i will give it some more thought

oh, wait… you’re not OP!!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.