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;
r937
October 24, 2022, 2:16pm
4
SELECT code
FROM yertable
GROUP
BY code
HAVING MIN(status) = 'C'
3 Likes
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
r937
October 24, 2022, 5:07pm
6
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
r937
October 24, 2022, 5:08pm
7
oh, wait… you’re not OP!!
system
Closed
January 24, 2023, 12:08am
8
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.