MySQL多个WHERE条件

Question

I'm trying to get this output with MySQL:
-Kate
-William
-dog
(3 results)

From those databases:

---------------    ---------------------- 
|  users      |    |         pet        |
|-------------|    |------|------|-------
| id  | Name  |    |  user|animal|number|
|-------------|    |------|------|------| 
| 1   |Kate   |    |  1   |  cat |   0  |
| 2   |William|    |  2   |  dog |   1  |
---------------    ----------------------

Number needs to be != 0
and I need to be able to make the difference between a fetch where number = 0 and number != 0

SELECT
    name, animal  
FROM
    users  
    INNER JOIN pet ON users.id = pet.user 
WHERE
    number != 0'

I can't get 'Kate' because never matching != 0.
I think I should use 2 different WHERE conditions in one request, but I don't know how...

Answer 1

First give your tables an alias because you have a ambigious column name id.

SELECT u.name, p.animal  
FROM users AS u
INNER JOIN pet AS p 
ON u.id = p.id  
WHERE p.number != 0

But what you asking for is to get results from both tables without join, right? Like this

SELECT
    name AS creatur,
    'users' AS type
FROM users  

UNION

SELECT
    animal AS creatur,
    'pet' AS type
FROM pet  
WHERE number != 0