Tricky Query Help

[Angel Cat]

I have 2 tables joined together by the IDs, People and the pets they
own

PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe

PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish

I have create another where the PETS are in one column separated by
semi-colons and removing the dups

NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish

What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists

THanks!

[Erland Sommarskog]

Angel Cat (ki***********@hotmail.com) writes:

I have 2 tables joined together by the IDs, People and the pets they
own

PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe

PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish

I have create another where the PETS are in one column separated by
semi-colons and removing the dups

NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish

What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists

Yes, this is tricky, because your new table violates a fundamental
rule in database design: no repeating groups. So I will not really
give an answer, because it would be ugly and ineffecient. Rather I
would recommend you to reconsider your design.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Vaxman]

See this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[--CELKO--]

>> I have 2 tables joined together by the IDs, People and the pets
they own <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Both your approaches are completely wrong. Try somethign more like
this:

CREATE TABLE People
(person_id CHAR(10) NOT NULL,
name CHAR(35) NOT NULL,
...);

CREATE TABLE Pets
(pet_id CHAR(10) NOT NULL,
name CHAR(35) NOT NULL,
...);

CREATE TABLE Ownership
(person_id CHAR(10) NOT NULL
REFERENCES People(person_id),
pet_id CHAR(10) NOT NULL
REFERENCES Pets (pet_id),
PRIMARY KEY (person_id, pet_id),
..);

Now you have to decide if onwership is 1:1 (as shown), 1:m or n:m.

I have create another where the PETS are in one column separated by

semi-colons and removing the dups <<

That is insane as well as a violation of 1NF. Why did you think of
this? What was your mental model of the data?

[malcolm]

I think you guys are missing the point of her question. My take is
that she is a beginner and does not even know what the next step
should be.

Celko,
You should leave your datamodel the way it is, that is only two
tables. You are done with this part. The other guys are right, you
should NOT create a 3rd table, rather you should write a resultset to
the existing data to morph into what you want. In order to do this
you can either create a view, a stored procedure or simply a direct
sql statement. I would probably create a stored procedure since this
is easily accessible from the calling application and has some
performance and security benifits.

Now there are two basic approaches you can take. You can either make
two really simple stored procedures (one for each table) that each
load in seperate objects in your code. Or make one stored procedure
that joins both tables together. If it's a quick and dirty
application, I would probably make one proc which could look something
like this:

create procedure up_somename_list
as

set nocount on

select pets.ID,
pets.PET,
people.[Name]
from PETS pets
join PEOPLE people on people.ID = pets.ID
order by people.[Name], pets.PET

-dave
jc*******@earthlink.net (--CELKO--) wrote in message news:<18**************************@posting.google. com>...

I have 2 tables joined together by the IDs, People and the pets they own <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Both your approaches are completely wrong. Try somethign more like
this:

CREATE TABLE People
(person_id CHAR(10) NOT NULL,
name CHAR(35) NOT NULL,
...);

CREATE TABLE Pets
(pet_id CHAR(10) NOT NULL,
name CHAR(35) NOT NULL,
...);

CREATE TABLE Ownership
(person_id CHAR(10) NOT NULL
REFERENCES People(person_id),
pet_id CHAR(10) NOT NULL
REFERENCES Pets (pet_id),
PRIMARY KEY (person_id, pet_id),
..);

Now you have to decide if onwership is 1:1 (as shown), 1:m or n:m.
I have create another where the PETS are in one column separated by

semi-colons and removing the dups <<

That is insane as well as a violation of 1NF. Why did you think of
this? What was your mental model of the data?