Geek asks and answers

What is the correct SQL statement to achieve the following result

I have the following table.

Owner_id    Owner
-----------------
1           Bill
2           Steve


Animal      Owner_fk_id
-----------------------
Cat         1
Dog         1
Lion        2

Car         Owner_fk_id
-----------------------
Ferrari     1
BMW         1
Lotus       2

I want to list all animals and cars for the owner with at least one Ferrari. Since Bill has a Ferrari, we will list all of Bill's cars and animals.

If I use the following SQL:

SELECT *
    FROM Owner
    LEFT JOIN Animal ON (Animal.Owner_fk_id = Owner.Owner_id )
    LEFT JOIN Car ON (Car.Owner_fk_id = Owner.Owner_id )
    WHERE Car.Car = 'Ferrari'

I will receive

Owner       Animal  Car
---------------------------
Bill        Cat     Ferrari
Bill        Dog     Ferrari

What is my desired result:

Owner       Animal  Car
---------------------------
Bill        Cat     Ferrari
Bill        Cat     BMW
Bill        Dog     Ferrari
Bill        Dog     BMW

Can I find out which correct SQL statement should I use?

+3
source share
2 answers

The inner join is in the required state (i.e. Car='Ferrari), and then the left join with the other tables as before.

SELECT o.Owner, a.Animal, c2.Car
FROM Owner as o
INNER JOIN Car as c1 on c1.Owner_fk_id = o.Owner_id and c1.Car='Ferrari'
LEFT JOIN Animal as a ON a.Owner_fk_id = o.Owner_id
LEFT JOIN Car as c2 ON c2.Owner_fk_id = o.Owner_id;
+3
source

Owner / , - :

SELECT subq.Owner, a.Animal, c2.Car FROM
    (SELECT o.* FROM Owner as o1 WHERE EXISTS
        (SELECT 1 FROM Car as c1
         WHERE c1.Owner_fk_id = o1.Owner_id
         AND c1.Car='Ferrari')
    ) as subq
LEFT JOIN Animal as a ON a.Owner_fk_id = subq.Owner_id
LEFT JOIN Car as c2 ON c2.Owner_fk_id = subq.Owner_id;
0

More articles:


All Articles