How to return result set based on other rows

I've 2 tables - packages and items. Items table contains all items belonging to the packages along with location information. Like following sample tables:

Packages table
id, type(enum{general,special})
1, general
2, special

Items table
id, package_id, location
1, 1, America
2, 1, Europe
3, 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,

1. 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)

2. for '开发者_JAVA技巧America' : package 1 should be returned since there are no special packages

---

Here are two different solutions: (Note: I called the enum field "package_type")

1st solution (via IF() function):

select 
  i.location, 
  if(ps.id is not null, ps.id, pg.id) as package_id
from 
  (select distinct location from Items) i
  inner join 
    (select i.location, p.id
     from Items i
       inner join Packages p on (i.package_id = p.id and p.package_type = 'general')
    ) pg on (i.location = pg.location)
  left join 
    (select i.location, p.id
     from Items i
       inner join Packages p on (i.package_id = p.id and p.package_type = 'special')
    ) ps on (i.location = ps.location)

This solution essentially takes the locations and joins it to the package with general (which is assumed to exist; hence inner join) and special package (which is optional; hence left join). It creates records such as this:

location | general-package | [special-package]

It then uses the MySQL IF function to first attempt to choose special package's ID, and then falls back to the general package's ID.

2nd solution (via casting of enum to integer):

select i.location, p.id
from
  (select i.location, max(cast(package_type as unsigned)) as package_type
   from Items i
     left join Packages p on (i.package_id = p.id)
   group by location
  ) i
  inner join 
    (select i.location, p.id, p.package_type
     from Items i
       inner join Packages p on (i.package_id = p.id)
    ) p on (i.location = p.location and i.package_type = p.package_type)

This solution exploits the fact that enums are stored as integers. It casts the enum to an integer. special in this case will return 2 and general will return 1. Because these special is guaranteed to be higher than general in this case (i.e. 2 > 1), we can use the MAX aggregate function. Now we essentially have a table of the locations and their "recommended package" (i.e. special if it exists, general otherwise). We simply join this to the normal query along with the expected package type, and it returns the correct results.

Disclaimer: I'm not sure about the efficiency of either of these methods, so you may want to test this on your own.

---

If you are looking to either redesign the table or to denormalize it for efficiency, I think this design may be more suitable:

GeneralPackages table
id, name
1, General Package 1

SpecialPackages table
id, name
1, Special Package 1
2, Special Package 2

Items table
id, general_package_id, special_package_id, location
1, 1, NULL, America
2, 1, 2, Europe

The advantage would be that it is easier to enforce several rules at the database level:

• A location must always have a general package (Items.general_package_id could be defined as NOT NULL)
• A location must only have a single general package (adding it in a field rather than a join guarantees that there is only one specified)
• A location may have at most a single special package (adding it in a field rather than a join guarantees that there is only one specified)
• A foreign key on Items.general_package_id = GeneralPackages.id would guarantee that that column only contains valid packages that are "general".
• The same thing could be done for special_package_id.

The disadvantage would be that you would probably need to use a UNION ALL every time you use one of your old queries.