grateful grateful - 7 months ago 15
SQL Question

Finding matches between identical MYSQL tables

An application matches ladies to gentlemen according only to the ladies’ criteria. There are 2 identically structured MySQL tables. The qualities of individuals are recorded with Boolean values.

TABLE 1: GentlemenQualities

ID / Handsome / Charming / Rich / Athletic / Fashionable

Peter / 1 / 1 / 1 / 0 / 0

George / 0 / 1 / 0 / 1 / 0

Mary is looking for a Charming and Handsome gentleman

TABLE2: LadiesCriteria

ID / Handsome / Charming / Rich / Athletic / Fashionable

Mary / 1 / 1 / 0 / 0 / 0

The following code finds a gentleman for Mary at the time that her new data is entered into the database:

SELECT ID FROM GentlemenQualities WHERE Handsome = 1 AND Charming = 1


We find Peter… We made a match! Peter was also rich, but that is not important to Mary.

Peter's row is DELETED from GentlemenQualities, and Mary's row DELETED from LadiesCriteria

Along comes Janice, she wants a fashionable gentleman, she doesn’t care about his other qualities

Janice / 0 / 0 / 0 / 0 / 1

SELECT ID FROM GentlemenQualities WHERE Fashionable = 1


This draws a blank. Janice is not matched yet.

Now there is a new entry in GentlemenQualities

Hans / 1 / 0 / 1 / 0 / 1

How do I match Hans to Janice as efficiently as possible? (there may be hundreds of unmatched ladies)

I'm using php & MySQLi.

Edu Edu
Answer

If the Ladies criteria is equal or less the Gentlemen criteria, then you should insert the new Gentlemen data on the following SQL query:

With Hans:

SELECT ID FROM LadiesCriteria
WHERE Handsome <= 1
AND Charming <= 0
AND Rich <= 1
AND Athletic <= 0
AND Fashionable <= 1

You would find:

Janice | 0 | 0 | 0 | 0 | 1

With Peter:

SELECT ID FROM LadiesCriteria
WHERE Handsome <= 1
AND Charming <= 1
AND Rich <= 1
AND Athletic <= 0
AND Fashionable <= 0

You would find:

Mary | 1 | 1 | 0 | 0 | 0

But not Janice, because she wants a Fashionable gentlemen.