SQL-根据几列将记录从一个表匹配到另一表 [英] SQL - match records from one table to another table based on several columns

问题描述

我有两个表:
爱好

I have two tables:
tblhobby

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

tblavailable_hobby

tblavailable_hobby

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

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

the sql query should take all the columns in tblhobby and match it with tblavailable_hobby. If all the hobbies match to the available_hobby, then the person is selected

查询应产生

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

请帮助

感谢您的回答.我已经继承了该数据库，目前无法对其进行规范化.但是，我想再提一个问题.假设:

Thanks for the answers. I have inherited this database and not able to normalize it at the moment. however, I would like to add another twist to the question. Suppose:

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

我想得到

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

我将如何处理?

推荐答案

DB设计欠佳，但是，假设您必须忍受它:

Poor DB design, but, assuming you have to live with it:

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

编辑:回答以下评论中提出的问题.

EDIT: Answering the twist proposed in the comments below.

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)

这篇关于SQL-根据几列将记录从一个表匹配到另一表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！