Play Matchmaker

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 (Figure) shows the names of each suitor and the has table (Figure) shows their qualities. The wltm table (Figure) shows the features that Juliet demands.

The suitor table

The has table

The wltm table

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,
    ->   FROM wltm CROSS JOIN suitor
    ->  WHERE'Juliet'
    ->    AND required_quality NOT IN
    ->     (SELECT has_quality FROM has WHERE;
| 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 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
    ->     FROM wltm, suitor
    ->    WHERE'Juliet'
    ->      AND required_quality NOT IN
    ->       (SELECT has_quality FROM has WHERE
    ->  );
| name  |
| Romeo |

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

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows