a newbie question regarding data selection

[davidklonski]

Hello

I am beginning to learn SQL and I am stuck trying to perform a simple query. Here are my 3 tables:

People:
ID: int(5)
Name: varchar(255)

Pets:
ID: int(5)
Name: varchar(255)
Type: varchar(255)

Ownership:
Person_ID int(5)
Pet_ID int(5)

The Ownership table makes the connection between a person and a pet.
I would like to extract the IDs of all the people who don't own a pet of type 'dog'. They are allowed to own pets of other types of course.

Please help, I am stuck with the mysql manual and going nowwhere

thanks

[itsyM]

Hello

I am beginning to learn SQL and I am stuck trying to perform a simple query. Here are my 3 tables:

People:
ID: int(5)
Name: varchar(255)

Pets:
ID: int(5)
Name: varchar(255)
Type: varchar(255)

Ownership:
Person_ID int(5)
Pet_ID int(5)

The Ownership table makes the connection between a person and a pet.
I would like to extract the IDs of all the people who don't own a pet of type 'dog'. They are allowed to own pets of other types of course.

Please help, I am stuck with the mysql manual and going nowwhere

thanks

How about:

SELECT Ownership.Person_ID
FROM Ownership
LEFT JOIN Pets ON Ownership.Pet_ID=Pets.ID
WHERE Pets.Type NOT LIKE 'Dog'

This propably isn't the most effective way when considering query optimization, but I THINK it works Please, feel free to correct and educate me on this

EDIT:

Here's another version that returns a table that lists the owner ID, owner name and the type of the pet she/he has. You can use this to test that your query really returns the right results:

SELECT
     People.ID AS Owner_ID, 
     People.Name AS Owner_Name, 
     Pets.Type AS Pet_type
FROM 
     People
LEFT JOIN 
     Ownership ON People.ID=Ownership.Person_ID
LEFT JOIN 
     Pets ON Ownership.Pet_ID=Pets.ID
WHERE 
     Pets.Type NOT LIKE 'Dog'

I used http://dev.mysql.com/doc/mysql/en/JOIN.html for reference. Hopefully this will help a bit!