Mysql query with different searches in the same table


What I'd like to do is search in a table with two different values, it's hard to explain so I will just give an example.

Table: people

| id     name    |
| 1      Bob     |
| 2      Jack    |
| 3      Waly    |
| 4      Alex    |

Table: animals

| id   person    key          value        |
| 1    1         dog          Terrier      |
| 2    1         dog          Shepherd     |
| 3    1         bird         African Grey |
| 4    3         cat          Toyger       |
| 5    3         cat          Korat        |
| 6    2         dog          Terrier      |

For example: I would like to be able to select just the people that have a dog that is a Terrier and a African bird so it should return 1 (Bob). I need to be able to add and remove parameters I may just want people who have a Terrier dog should return 1 (Bob) and 2 (Jack).

I have tried basic sql but have gotten it to work because when you limit the key you can search another one. The following query is what I have tried and I want to return: 1 (Bob).

FROM people p, animals a
WHERE = a.person
AND (a.key = 'dog' AND a.value LIKE '%Terrier%' )
AND (a.key = 'bird' AND a.value LIKE '%African%' )

If at all possible I would like to keep all of the animals rows in the same table so I don't have to separate them out. Thanks for all of your help!

You'll need multiple table lookups, each searching for a particular animal. For example, using a double join:

select  *
from    people p
join    animals a1
on      a1.person =
join    animals a2
on      a2.person =
where   a1.key = 'dog' and a1.value like '%Terrier%'
        and a2.key = 'bird' and a2.value like '%African%'

Or a double exists:

select  *
from    people p
where   exists
        select  *
        from    animals a
        where   a.person =
                and a.key = 'dog'
                and a.value like '%Terrier%'
        and exists
        select  *
        from    animals a
        where   a.person =
                and a.key = 'bird'
                and a.value like '%African%'