如何从表中选择记录，其中记录具有来自另一个表的最相似属性

Question

TABLE A
result   select
A              a
B              a
B              b
C              a
C              b
C             c

TABLE B

user   select

1              a
1              b
2              b
2              c

TABLE C
user   result
1         B
2         C


what mysql query should i have to use, if i want to do like this:

if user select a the result is A B C (because A B C have 'a' in column 'select') if user select a b the result is B C (because A doesn't have 'b' in column 'select') if user select a b c the result is C (because C have 'a b c' in column 'select') if user select b c the result is C (because A B doesn't have 'c' in column 'select') if user select c the result is C (because A B doesn't have 'c' in column 'select')

so it will select the record that have the most similar from what user selected and ignore the other record that doesn't match.. sorry for my english, i'm speak bahasa...

Answer 1

SELECT  `result`
FROM tableA
WHERE   `select` IN ('a','b','c')
GROUP BY result
HAVING COUNT(*) = 3

if a unique constraint was not enforce on select for every result, a DISTINCT is required.

SELECT  `result`
FROM tableA
WHERE   `select` IN ('a','b','c')
GROUP BY result
HAVING COUNT(DISTINCT `select`) = 3