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 wish to list out all the animals and cars, for a owner with at least 1 Ferrari. Since Bill is having a Ferrari, we will list out all Bill's cars and animals.

If I am using the following SQL :

SELECT *
    FROM Owner
    LEF开发者_开发知识库T 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 be getting

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

What my wished result is

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

May I know what is the correct SQL statement I should be using?

Inner join on your required condition (ie. Car='Ferrari) and then left join 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;

If an Owner owns multiple Ferraris and it's important to only count him/her once, you could do something like this:

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;