yanman1234 yanman1234 - 1 year ago 97
MySQL Question

MySQL Select Items with Only One Row Flagged

I have a situation similar to the following:

petId | name | option | optionAvail
1 | Dog | Leash | 1
2 | Dog | Crate | 0
3 | Cat | Leash | 1
4 | Cat | Box | 1

I want a query that if I specify an option, it should return all names with that being the only available option. So for example if I specify Leash I'd want only Dog returned since Leash is the only available option for Dog, where Cat also has Box available. The idea is a user is notified of names that'll have no more available options currently applied if they make one unavailable. This should return any number of names that fit this criteria, but will only ever have one option specified at a time (Can't specify Leash and Crate for same query). This seems like a simple query to perform but it just isn't coming to me.

Answer Source

You can use conditional aggregation for this:

select name
from pets
where optionAvail = 1
group by name
having count(case when `option` != 'Leash' then 1 end) = 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download