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;Fis h
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! 4 1477
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;Fis h
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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
>> 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_i d),
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?
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_lis t
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*******@earth link.net (--CELKO--) wrote in message news:<18******* *************** ****@posting.go ogle.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_i d), 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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dracolytch |
last post by:
Good day all,
Ok, I have a pretty tricky problem that I need some help with.
I pass around search query information a fair amount (specifically
WHERE statements). Normally, I just rawurlencode() the buggers, and
pass them via the URL. I like having the where clauses in the URL,
because then someone can just bookmark the URL, or send it to a friend,
and I don't have to worry about a thing.
If someone does a search that requires a LIKE...
|
by: Joel Thornton |
last post by:
I'm having much difficulty figuring out how to write the following
query. Please help!
I have this table:
Event
EventId int Primary Key
PatientId int
SeverityLevel int
|
by: scott |
last post by:
Hello, I'm trying to find the most optimal way to perform a tricky
query. I'm hoping this is some sort of standard problem that has been
solved before, but I'm not finding anything too useful so far. I have
a solution that works (using subqueries), but is pretty slow.
Assume I have two tables:
ItemID int (Primary Key)
ItemSourceID int
ItemUniversalKey uniqueidentifier
|
by: Pea |
last post by:
I'm working with a system usage database. I want to filter out
repetitive logins. The query I have retrieves data like this:
USER_DATE USER_TIME1 USER_USERID USER_ACCOUNT
10/01/2004 19:56 y708ga27 The Capital Group
10/01/2004 19:58 y708ga27 The Capital Group
10/01/2004 19:59 y708ga27 The Capital Group
10/01/2004 20:19 y708ga27 The Capital Group
In a case like this - with consecutive logins for the same user, if
|
by: pointBoarder |
last post by:
Thanks in advance to all who read this.
I've got 3 tables which were created from a txt file dumped from some
old system.
Header
ID -- autonumber, primary
OrderNum -- field I want
Line
| |
by: ric_deez |
last post by:
Hi there, I would like to create a simple search form to allow users to
search for a job number based on a number of parameters. I think I
understand how to use parameteres associated with Stored Procedures
with a data reader to add various parameters.
However, if I have a stored procedure such as
CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype, @par3
datatype)
|
by: MorrganMail |
last post by:
Or at least I find it tricky. :-)
Assume we have three tables A, B and C. Table A contains a path and the
distance for traveling that path:
A (PathId, NodeId, Dist (from previous node))
1, 1, 0
1, 2, 10
1, 3, 5
|
by: edouard.spooner |
last post by:
Hi,
I have a tricky SQL query problem that I'm having probs with.
I have a table which resembles something like this
Date | Price1 | Price2 | Price3
01 Jan 2006 | 100 | 100 | 100
02 Jan 2006 | 100 | 100 | 100
03 Jan 2006 | 100 | 100 | 100
|
by: KevLe |
last post by:
I'm building a log search function in c# for a certain management app and would like some help on the design how to solve
this, here is my solution (on paper) so far:
The log files are saved to disk, one file per date and one row "per logged event". Each row has a specific logged time
(within the file's date) with different keys and values.
The search filter query is built up via comboboxes and a datagridview. The datagridview is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |