6

Prerequisites

I have two tables. A list of people in one table, and how they prefer each other in a foreign key lookup table. The first table is only the list of people. The other is where they all list a few other people they would prefer to have as a roommate.

Table People:

  • List of people with ID, name and surname, etc

Table Choices:

  • List of choosers (FK People ID)
  • List of chosen ones (FK People ID)

Question

How can I list matches with SQL (or PHP)? That is, where one person is also on the list on the person he wanted to have as a roommate? Basically you have a chooser with a list of chosen ones. How would you check if the chooser is also on the list of one of his or her chosen ones?

Basically I want a report with every stable match, that is where the chooser is also on the list of at least one of his or her chosen ones.

I am guessing a for loop would do the trick, but how would you even put together the first iteration? Much less the rest of the loop?

Kebman
  • 1,901
  • 1
  • 20
  • 32
  • 3
    Better to show your code so we can give specific answers. – Brett Jan 19 '13 at 21:24
  • 1
    That's the problem. I don't even know where to begin. Inner join? Outer? Some sort of where-statement? However I have a very spesific dataset. The problem is how to report the matches. – Kebman Jan 19 '13 at 21:27
  • @Kebman the thing is, we like to see people try. And if they fail, we can provide *specific* answers or even send them into different directions. Reopen vote discarded. – Lukas Knuth Mar 12 '13 at 19:55

3 Answers3

5

Join based solution:

SELECT
    r1.name as name1,
    r2.name as name2
FROM
    roommate r1
JOIN
    roommate_pair rp1 ON r1.id = rp1.chooser_id
JOIN
    roommate r2 ON r2.id = rp1.choosen_id
JOIN
    roommate_pair rp2 ON r2.id = rp2.chooser_id
WHERE
    rp2.choosen_id = r1.id
GROUP BY
    CONCAT(GREATEST(r1.id,r2.id),'-',LEAST(r1.id,r2.id))

Last GROUP BY is to remove duplicate matches in swapped columns. Working SQL Fiddle

dev-null-dweller
  • 29,274
  • 3
  • 65
  • 85
  • This work very well, and it also seems like something that's easy to expand upon. Thank you! :) – Kebman Jan 20 '13 at 02:09
  • Just wondering about the syntax. Where do r1. and rp1 come from? Are these simply aliases? – Kebman Jan 20 '13 at 22:22
  • Yes, aliases goes after tablename and are necessary when joining same table more than once - you need to specify column from one of them and depending on join condition it may vary for one row, just as shown in this case. – dev-null-dweller Jan 20 '13 at 23:41
2
SELECT a.chooser, a.chosen
FROM roommates a,roommates b
WHERE a.chooser = b.chosen
AND a.chosen = b.chooser;

Using the above query you should get the cross-referenced id's... You do, however, get doubles (both references are returned). See SQL Fiddle. You could do a check on that in your PHP-code.

Marty McVry
  • 2,838
  • 1
  • 17
  • 23
1

This piece of code should provide you some hint. First you will iterate through all the people. Then from the list of possible preferred people, you select only those who, in turn, have the original person in their list of preferred people. 

for cc in (select * from people) loop
  for dd in (select * from preferences pr where pr.source_id = cc.people_id and exists (select 1 from preferences pr1 where pr1.source_id = pr.friend_id and pr1.friend_id = cc.people_id)) loop
    --do your stuff here
  end loop
end loop
GeorgeVremescu
  • 1,253
  • 7
  • 12