help in formulating a query

davidklonski

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

dartcol

I don't know whether what you are doing is possible or not but I'd go about it a different way. I'm not that experienced with MySQL myself but here goes ...

Table 1 - person
id   name
1    John
2    Sarah
3    Julia
4    Pete


Table 2 - persontopet
person_id    pet_id
1            1
1            2
1            3
2            4
2            5
3            6
4            7


Table 3 - pet
id   name     type
1    fluffy   cat
2    bert     dog
3    rover    cat
4    minx     dog
5    daffy    dog
6    snowy    cat
7    gerrof   dog

So we know that owner 1 (John) has three pets (pets 1,2 and 3) the names of which are fluffy, bert and rover and that fluffy and rover and cats and bert's a dog.

Now, I don't know whether this is the most efficient of tables as I've learned bot mysql and php hands-on and haven't had too much time to consider theory too much but this kind of relation between tables seems to be the most logical and sensible. Feel free to shout me down. I'm learning too. 🙂

davidklonski

actually this is the solution I came up with.

The only thing that bothers me is that solution defines a many - many relationship between people and pets.

I would like to have a one - many relationship where a person may have multiple pets, but a pet belongs to a single person

dartcol

I can't see any other way of doing it though. You only want one entry for the owner yet owners may have multiple pets. Therefore, it's necessary to have a table linking the single owner to the many pets. Put this way isn't this the one - many relationship you're after? I'm happy to be put wrong. It might help my database strategy, too!

[Edit - Yep, I was wrong. It's a many to many relationship as you said. So how would a person do a one to many relationship, I wonder too!]