user3186459 user3186459 - 7 months ago 10
SQL Question

How to use query irelationship in SQL

My problem is this:

I have a table "users":

id_us | name
-------------
1 | bob
2 | ken
3 | jones


and I also have a table for pets "pets"

id_pet | pet
-------------
1 | dog
2 | cat
3 | fish


and a table for storing the relation of user and pets "user_pets"

id | id_us | pet
-------------------
1 | 1 | 1 --------> (dog)
2 | 1 | 2 --------> (cat)
3 | 2 | 1 --------> (dog)
4 | 3 | 3 --------> (fish)
3 | 3 | 2 --------> (cat)


I have been trying to create a query that gives me all the users that do not have dogs, the problem of my query is that because the user "Bob" for example has two pets and one of them is not a dog, it is returned in my result for the query even if he is a dog owner.

Query:

SELECT
usuario.name
FROM
usuario
JOIN
user_pets ON usuario.id_us = user_pets.id_us
WHERE
user_pets.pet != 1
GROUP BY
usuario.name

Answer

You might consider using EXISTS

Inner Query bring all the dogs from the users.

Then if NOT EXISTS any result mean Bob doesnt have a dog.

SELECT *
FROM users u
WHERE NOT EXISTS ( SELECT *
                   FROM user_pets up
                   WHERE up.id_pet = 1 
                   AND up.id_us = u.id_us )