SitePoint Sponsor

User Tag List

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

    help in formulating a query

    Hi

    I am new to SQL and are faced with my first complex query.
    I would appreciate if someone could help me with the syntax for the following query:

    I have two tables: People & Pets
    Here are the columns for the People table:
    ID int(11)
    Name varchar(20)
    Dogs varchar(255)
    Cats varchar(255)

    Here are the columns for the Pets table:
    ID int(11)
    Name varchar(255)

    Here is how people are related to pets:
    The Dogs column is a camma-delimited list of all the dogs IDs that the person owns.
    The Cats column is a camma-delimited list of all the cats IDs that the person owns.

    If the following row exists in the People table:
    ID = 1
    Dogs = 1,2,
    Cats = 4,5,
    It means that person 1 has two dogs (the dogs IDs are 1 & 2) and two cats (the cats IDs are 4 & 5).

    Here is what I am trying to do:
    extractd all the people who have a dog, and sort the list by the dog name.

    I would appreciate any help.
    If someone thinks that a better arrangement for the tables is in order, please let me know. I am still learning.

    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you will not be able to do what you want in sql

    a comma-delimited list violates first normal form

    it is not impossible, but it is horribly complex and inefficient sql

    redesign your table and you will see how easy the sql is

    create table people
    ( id int not null primary key auto_increment
    , name varchar(20)
    );
    create table pets
    ( id int not null primary key auto_increment
    , name varchar(20)
    );
    create table peoplepets
    ( person_id int not null
    , pet_id int not null
    , primary key ( person_id, pet_id )
    , constraint validperson
    foreign key ( person_id ) references people ( id )
    , constraint validpet
    foreign key ( pet_id ) references pets ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you will not be able to do what you want in sql

    a comma-delimited list violates first normal form

    it is not impossible, but it is horribly complex and inefficient sql

    redesign your table and you will see how easy the sql is

    create table people
    ( id int not null primary key auto_increment
    , name varchar(20)
    );
    create table pets
    ( id int not null primary key auto_increment
    , name varchar(20)
    );
    create table peoplepets
    ( person_id int not null
    , pet_id int not null
    , primary key ( person_id, pet_id )
    , constraint validperson
    foreign key ( person_id ) references people ( id )
    , constraint validpet
    foreign key ( pet_id ) references pets ( id )
    );
    OK, thanks

    one last question. Given the tables you suggested, how do I generate a table with the following as columns:
    preson ID, person name, dog pet ID and dog pet name

    If a person has more than one dog, display the first one

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh yeah, pet type, sorry forgot about that
    Code:
    alter table pets
    add column pettype varchar(9);
    as for "If a person has more than one dog, display the first one" perhaps you could say how you want "the first one" to be determined, given that there is no sequence of rows within a relational database table

    anyhow, here's the query to return people and their dogs
    Code:
    select people.id     as personid
         , people.name   as personname 
         , pets.id       as dogpetid
         , pets.name     as dogpetname
      from people
    inner
      join peoplepets
        on people.id 
         = personpets.person_id 
    inner
      join pets
        on personpets.pet_id 
         = pets.id
     where pets.pettype = 'dog'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    select people.id     as personid
         , people.name   as personname 
         , pets.id       as dogpetid
         , pets.name     as dogpetname
      from people
    inner
      join peoplepets
        on people.id 
         = personpets.person_id 
    inner
      join pets
        on personpets.pet_id 
         = pets.id
     where pets.pettype = 'dog'
    Is it possible to obtain both a dog info and a cat info in the same query?
    here is the table output I am looking for:
    person ID | person Name | Dog ID | Dog name | Cat ID | Cat Name

    or do I need to queries to accomplish that?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, you can, but the query is complex, unmaintainable, and inefficient

    if you want to denormalize your data, the best place to do that is in your application program, after you ahve returned the simple data from the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yeah, you can, but the query is complex, unmaintainable, and inefficient

    if you want to denormalize your data, the best place to do that is in your application program, after you ahve returned the simple data from the database
    So your suggestion would be:
    Query 1: obtain user data
    Query 2: obtain dog data based on the person data
    Query 3: obtain cat data based on the person data

    is that right?
    I would need 3 queries in order to keep it simple. Would that be that best solution?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, that's not my suggestion

    my suggestion is in post #4

    remove the WHERE clause if you want both dogs and cats

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    no, that's not my suggestion

    my suggestion is in post #4

    remove the WHERE clause if you want both dogs and cats

    but then I wouldn't know if the name is of a dog or of a cat...
    I want the names of the dog listed first, and then the names of the cats

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not to be rude or anything, but when are you gonna try a query on your own?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    not to be rude or anything, but when are you gonna try a query on your own?
    sorry

    I am trying constantly with the MySQL manual by my side.
    I didn't mean to bother you, just got stuck

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select people.id     as personid
         , people.name   as personname 
         , pets.pettype
         , pets.id       as petid
         , pets.name     as petname
      from people
    inner
      join peoplepets
        on people.id 
         = personpets.person_id 
    inner
      join pets
        on personpets.pet_id 
         = pets.id
    order
        by people.name 
         , pets.pettype desc
         , pets.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •