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
Name
Romeo
Paris


The has table
namehas_quality
RomeoNS
RomeoGSOH
RomeoVeronian
RomeoM
ParisNS
ParisM


The wltm table
namerequires_quality
JulietNS
JulietM
JulietGSOH


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



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