CODEWITHSUNDEEP
sqlms-access
Dave
Dave

Reputation: 3717

How to get the Joins right in a SQL query

I have two tables, as follows:

People:

First_Name Last_Name
  Bill       Smith
  David      Williams
  Fred       Jones
  Freda      Jones
  Jane       Smith
  John       Smith
  Peter      Williams
  Sally      Williams

Pets:

Species Title  Owner
  Dog    Fido   Jones
  Cat    Tibs   Jones
  Dog    Jock   Williams

I know it's a weird database design, but please don't tell me to redesign the database. It's an example that represents the real problem, which is more complicated and can't be redesigned.

I am trying to formulate a query that will return exactly one row for each person, and a column containing the title of their Cat or null if they do not own a cat (I used Title because Name is a reserved word and Access complained).

The nearest I have come up with is:

SELECT People.*, Pets.Title
FROM People 
LEFT JOIN Pets ON People.Last_Name = Pets.Owner
WHERE Pets.Species IS NULL OR Pets.Species = "Cat"

However this doesn't list any rows for the Williams family because they own a dog.

I have tagged this as MS Access because I am actually using Access 97 (don't ask!), but, other than Access's rather limited Join facilities, it should be a general SQL question.

Upvotes: 1

Views: 52

Answers (4)

Dave
Dave

Reputation: 3717

For posterity - I finally got it:

SELECT People.*, Pets.Title FROM 
People LEFT JOIN (SELECT * FROM Pets WHERE Species='Cat') AS PeopleWithCats 
ON People.Last_Name=PeopleWithCats.Owner

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

EDIT: The left join syntax in Access causes trouble, which means that for access we need to involve a subquery. This will look something like (the untested);

SELECT People.*, Pets.Title
FROM People LEFT JOIN 
  [SELECT Pets.* FROM Pets WHERE Pets.Species = 'Cat']. AS Pets 
    ON People.Last_Name = Pets.Owner

-- Original answer --

What you seem to want is a regular left join with an additional condition that the pet needs to be a cat;

SELECT People.*, Pets.Title
FROM People LEFT JOIN Pets 
  ON People.Last_Name = Pets.Owner AND Pets.Species = 'Cat'

The reason for not putting the cat condition in a WHERE clause is that a where clause eliminates all non matches, while putting it in the ON clause makes the result NULL if it can't find a match (just as you describe your desired result)

Upvotes: 5

Akshey Bhat
Akshey Bhat

Reputation: 8545

Try this

SELECT People.*, case when Pets.Species = 'Cat' then Pets.Title else null end as Title
 FROM People LEFT JOIN Pets ON People.Last_Name = Pets.Owner

Upvotes: 0

David
David

Reputation: 218818

this doesn't list any rows for the Williams family because they own a Dog

That's because of the WHERE clause:

WHERE Pets.Species IS NULL OR Pets.Species = "Cat"

If Pets.Species is "Dog" then this would evaluate to false, so those records are not shown. First remove the clause entirely:

SELECT People.*, Pets.Title
FROM People
LEFT JOIN Pets ON People.Last_Name = Pets.Owner

Now you're getting the records you want, but not specifically the data you want. The Title column is showing any value, and you only want it to show "Cat" or NULL. You can adjust the JOIN to achieve that:

SELECT People.*, Pets.Title
FROM People
LEFT JOIN Pets ON People.Last_Name = Pets.Owner
              AND Pets.Species = 'Cat'

This will filter the joined table, but not the entire result set.

Upvotes: 1

Related Questions