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.

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].