How to display Ford owners that don't own Ferarris in SQL
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 = 'Fera开发者_StackOverflowrri' )
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
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')
精彩评论