How to display Ford owners that don't own Ferarris in SQL

Question

Hi,

I'm learning SQL and I've written a query that returns all owners of Fords. How do I amend the query so that it doesn't return anyone who owns a Ford and a Ferarri?

SELECT  DISTINCT Owns.Pid
FROM (Person INNER JOIN Owns ON Person.Pid= Owns.Pid) INNER JOIN Car ON Owns.Cid=Car.Cid
WHERE Car.Manufacturer = 'Ford' 

Added:

I tried the following code to see if I could produce just people who didn't own Ferarris, but it produces a blank datasheet. Any ideas what's wrong with it?

SELECT  DISTINCT Owns.Pid
FROM (Person INNER JOIN Owns ON Person.Pid= Owns.Pid) INNER JOIN Car ON Owns.Cid=Car.Cid
WHERE NOT EXISTS
(SELECT  Owns.Pid
FROM (Person INNER JOIN Owns ON Person.Pid= Owns.Pid) INNER JOIN Car ON Owns.Cid=Car.Cid
WHERE Car.Manufacturer = 'Ferarri' )

Answer 1

Since this is marked homework, here are some hints:

• Find the Ids of all persons who own a Ferrari (should be almost identical to your original query)
• Use this query in a sub-query (or join) to exclude them from your original query

Answer 2

SELECT 

  FORDOWNERS.PID

FROM 

  (SELECT  DISTINCT Owns.Pid
  FROM (Person INNER JOIN Owns ON Person.Pid= Owns.Pid) INNER JOIN Car ON Owns.Cid=Car.Cid
  WHERE Car.Manufacturer = 'Ford') AS FORDOWNERS

INNER JOIN 

  (SELECT  DISTINCT Owns.Pid
  FROM (Person INNER JOIN Owns ON Person.Pid= Owns.Pid) INNER JOIN Car ON Owns.Cid=Car.Cid
  WHERE Car.Manufacturer = 'Ferrari') AS FERRARIOWNERS

ON FORDOWNERS.PID = FERRARIOWNERS.PID

or like this

SELECT 

  OWNS.PID

FROM 

  Person INNER JOIN Owns ON Person.Pid= Owns.Pid INNER JOIN Car ON Owns.Cid=Car.Cid
  WHERE Car.Manufacturer = 'Ford'
  AND OWNS.PID IN (SELECT  DISTINCT Owns.Pid
                   FROM (Person INNER JOIN Owns ON Person.Pid= Owns.Pid) INNER JOIN Car ON Owns.Cid=Car.Cid
                   WHERE Car.Manufacturer = 'Ferrari')