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.

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