SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a newbie question regarding data selection

    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

  2. #2
    SitePoint Zealot itsyM's Avatar
    Join Date
    Jul 2002
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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
    How about:

    Code:
    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:

    Code:
    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!
    Last edited by itsyM; Apr 25, 2004 at 03:14.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •