mysql - SQL - 根据多列将一个表中的记录匹配到另一个表

Question

我有两个表:
爱好

   +-------+-------+-------+-------+  
   | name  |hobby1 |hobby2 |hobby3 |  
   +-------+-------+-------+-------+   
   | kris  | ball  | swim  | dance |  
   | james | eat   | sing  | sleep |  
   | amy   | swim  | eat   | watch |  
   +-------+-------+-------+-------+

tblavailable_hobby

+----------------+ 
| available_hobby|
+----------------+
| ball           | 
| dance          | 
| swim           | 
| eat            | 
| watch          | 
+----------------+ 

sql 查询应该获取 tblhobby 中的所有列并将其与 tblavailable_hobby 匹配。如果所有爱好都与 available_hobby 匹配，则选择该人

查询应该产生

+--------+ 
| name   |
+--------+
| kris   | 
| amy    | 
+--------+

请帮忙

感谢您的回答。我继承了这个数据库，目前无法对其进行规范化。但是，我想为这个问题添加另一个转折点。假设:

   +-------+-------+-------+-------+  
   | name  |hobby1 |hobby2 |hobby3 |  
   +-------+-------+-------+-------+   
   | kris  | ball  | swim  | dance |  
   | james | eat   | sing  | sleep |  
   | amy   | swim  | eat   | watch | 
   | brad  | ball  |       | dance |
   +-------+-------+-------+-------+

我想得到

+--------+ 
| name   |
+--------+
| kris   | 
| amy    | 
| brad   |
+--------+

我将如何处理它？<​​/p>

Answer 1

糟糕的数据库设计，但是，假设您必须忍受它:

SELECT h.name
    FROM tblhobby h
        INNER JOIN tblavailable_hobby ah1
            ON h.hobby1 = ah1.available_hobby
        INNER JOIN tblavailable_hobby ah2
            ON h.hobby2 = ah2.available_hobby
        INNER JOIN tblavailable_hobby ah3
            ON h.hobby3 = ah3.available_hobby

编辑:回答下面评论中提出的扭曲。

SELECT h.name
    FROM tblhobby h
        LEFT JOIN tblavailable_hobby ah1
            ON h.hobby1 = ah1.available_hobby
        LEFT JOIN tblavailable_hobby ah2
            ON h.hobby2 = ah2.available_hobby
        LEFT JOIN tblavailable_hobby ah3
            ON h.hobby3 = ah3.available_hobby
    WHERE (h.hobby1 IS NULL OR ah1.available_hobby IS NOT NULL)
        AND (h.hobby2 IS NULL OR ah2.available_hobby IS NOT NULL)
        AND (h.hobby3 IS NULL OR ah3.available_hobby IS NOT NULL)