How to return result set based on other rows
- by understack
I've 2 tables - packages and items. Items table contains all items belonging to the packages along with location information. Like this:
Packages table
id, name, type(enum{general,special})
1, name1, general
2, name2, special
Items table
id, package_id, location
1, 1, America
2, 1, Africa
3, 1, Europe
4, 2, Europe
Question: I want to find all 'special' packages belonging to a location and if no special package is found then it should return 'general' packages belonging to same location.
So,
for 'Europe' : package 2 should be returned since it is special package (Though package 1 also belongs to Europe but not required since its a general package)
for 'America' : package 1 should be returned since there are no special packages