SQL - match records from one table to another table based on several columns

Question

I have two tables:
tblhobby

   +-------+-------+-------+-------+  
   | 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          | 
+----------------+ 

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

the query should produce

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

Please help

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 |
   +-------+-------+-------+-------+

I would like to get

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

how would i go about with it?

Answer 1

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)

Answer 2

I know this doesn't answer your question directly, and others have pointed out that your table design is problematic. What it should look like is this:

Table: Person
Id    Name
-------------
1     Kris
2     James
3     Amy

table: PersonHobby (Join table)
PersonId HobbyId
----------------
1        1 -- Kris likes to ball
1        2 --   "           dance
1        3 --   "           swim
2        4  -- James likes to eat

Table: Hobby
Id   Name
--------------
1    Ball
2    Dance
3    Swim
4    Eat
etc.

This design uses the concept of a Join or Junction table that allows you make many-to-many relationships between data. In this case people and hobbies.

You then query the data like this:

SELECT * 
FROM Person p 
JOIN PersonHobby AS ph on p.Id = ph.PersonId
JOIN Hobby       AS h  on h.Id = ph.HobbyId

WHERE ... -- filter as you need to

The PersonHobbies table in my example takes a table of Persons and a table of Hobbies and enables relationships between Persons and Hobbies. I know this will probably look like more work to you... extra tables, extra columns. But trust us, this design will make your life much simpler in the near future. In fact, you're already feeling the pain of your design by trying to figure out a query which should be much simpler than it is against your current db.

I would like to produce a WHERE filter to match your requirements but I don't quite understand what you're after. Could you explain in some more detail?

Answer 3

You can use a query to transform your existing table into a "virtual table", which I think should be easier to work with. Save this SQL statement as qryHobbiesUnion.

SELECT [name] AS person, hobby1 AS hobby
FROM tblhobby
WHERE (((hobby1) Is Not Null))
UNION
SELECT [name], hobby2
FROM tblhobby
WHERE (((hobby2) Is Not Null))
UNION
SELECT [name], hobby3
FROM tblhobby
WHERE (((hobby3) Is Not Null));

I enclosed "name" in square brackets because it's a reserved word. And I aliased [name] as person to avoid problems with square brackets when using qryHobbiesUnion in a subquery later.

I assumed any "empty" values for hobby will be Null. If blanks could also be empty strings (""), change the WHERE clauses to a pattern like this:

WHERE Len(hobby1 & "") > 0

After you determine which version of the WHERE clause returns the correct rows, save the query and use it in another query.

SELECT sub.person
FROM
    [SELECT qh.person, qh.hobby, ah.available_hobby
    FROM
        qryHobbiesUnion AS qh
        LEFT JOIN tblavailable_hobby AS ah
        ON qh.hobby = ah.available_hobby
        ]. AS sub
GROUP BY sub.person
HAVING (((Count(sub.hobby))=Count([sub].[available_hobby])));

Using your second set of sample data, that query returns the 3 person names you wanted: amy; brad; and kris.

If tblhobby contained a row for a person with all the hobby fields empty, this query would not include that person's name. That makes sense to me because it seems your intention is to identify the people whose hobby choices are all matched in tblavailable_hobby. So a person with no hobby selections has no matches. If you want different behavior, this will probably get uglier. :-)

Answer 4

Really you must learn more about relational databases. Your design isn't good. You should have table with people and a table with hobbies. Then you should have a table the relates the two tables by an ID.

Your tables should look likes this

TABLE: People COLUMNS: PID (INT, Primary Key), NAME

TABLE: Hobbies COLUMNS: HID (INT, Primary Key), Hobby

TABLE: PeoplesHobbies COLUMNS: ID, PID, HID

THEN your query would look something like this

select * from people `p` inner join PeoplesHobbies `ph` on p.PID = ph.PID inner join on Hobbies `h` on ph.HID = h.HID where p.NAME = 'JOHN'

Answer 5

SELECT name
FROM tblhobby AS h
WHERE EXISTS
        ( SELECT *
          FROM tblavailable_hobby AS ah1
          WHERE h.hobby1 = ah1.available_hobby
        )
  AND EXISTS
        ( SELECT *
          FROM tblavailable_hobby AS ah2
          WHERE h.hobby2 = ah2.available_hobby
        )
  AND EXISTS
        ( SELECT *
          FROM tblavailable_hobby AS ah3
          WHERE h.hobby3 = ah3.available_hobby
        )

Answer 6

Borrowing from Joe's answer:

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)

ypercube's answer can be similarly extended.