Hack 56. Play Matchmaker

Romeo is an NS GSOH M Veronian (nonsmoking, good-sense-of-humor male who lives in Verona). Juliet WLTM (would like to meet) an NS GSOH M. Will Romeo do?

The suitor table (Table 7-16) shows the names of each suitor and the has table (Table 7-17) shows their qualities. The wltm table (Table 7-18) shows the features that Juliet demands.

Table 7-16. The suitor table

Name
Romeo
Paris

Table 7-17. The has table

name has_quality
Romeo NS
Romeo GSOH
Romeo Veronian
Romeo M
Paris NS
Paris M

Table 7-18. The wltm table

name requires_quality
Juliet NS
Juliet M
Juliet GSOH

Romeo is a suitable partner because he has all three qualities that Juliet required. Paris does not have the GSOH quality, so he should not be considered.

You can solve this problem by finding the unsuitable suitors first. For each required quality you find the suitors who do not have that quality:

mysql> SELECT required_quality, suitor.name
 -> FROM wltm CROSS JOIN suitor
 -> WHERE wltm.name='Juliet'
 -> AND required_quality NOT IN
 -> (SELECT has_quality FROM has WHERE name=suitor.name);
+------------------+-------+
| required_quality | name |
+------------------+-------+
| GSOH | Paris |
+------------------+-------+

Notice that every line of the wltm table is compared against every line of the suitor table. Also notice that the suitor.name value is referenced both inside and outside the subqueryyou may find that this query will perform better if you transform it into a JOIN [Hack #10].

Having discovered that Paris is the only unsuitable suitor you can deduce that Romeo must be suitable. To do this in SQL you need to find the table difference. You can include the phrase NOT IN to select all rows from suitor excluding the names from the previous query:

mysql> SELECT name FROM suitor
 -> WHERE name NOT IN
 -> (SELECT suitor.name
 -> FROM wltm, suitor
 -> WHERE wltm.name='Juliet'
 -> AND required_quality NOT IN
 -> (SELECT has_quality FROM has WHERE name=suitor.name)
 -> );
+-------+
| name |
+-------+
| Romeo |
+-------+

You can find another approach to this problem in "Choose Any Three of Five" [Hack #89].

SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net