Reputation: 191
I have an sql command:
select location,status,total from my_table
This return:
location - status - total
city a - active - 3
city a - pending - 2
city a - cancel - 4
city b - pending - 4
city b - cancel - 4
city c - active - 2
city c - cancel - 6
How can i return only the rows with cities where having at least once the status active and total >= 1?
So, i want to return:
location - status - total
city a - active - 3
city a - pending - 2
city a - cancel - 4
city c - active - 2
city c - cancel - 6
As you notice i dont want to return the city b cause it hasn't the status active at least one time with total >= 1.
Upvotes: 3
Views: 1460
Reputation: 1334
select location,status,total from my_table
where location in (
select location from my_table
where status = 'active'
and total >= 1
)
Upvotes: 1