|
|
Posted by --CELKO-- on 03/10/07 13:35
>> I'm sure this has been brought up many times, but I will ask anyway. <<
Yes it is brought up all the time because people will not bother to
read even one book on RDBMS, so they keep asking this kind of thing
over and over. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Let's start by doing what you should
have done for us:
CREATE TABLE Owners -- plural if you have more than one
(owner_id INTEGER NOT NULL PRIMARY KEY,
owner_name CHAR(20) NOT NULL);
CREATE TABLE Dogs -- plural if you have more than one
(dog_id INTEGER NOT NULL PRIMARY KEY,
dog_name CHAR(20) NOT NULL);
Why did you think that an owner is an attribute of a dog? It is a
relationship! It might have attributes of its own, like license
numbers, issue date, etc. but let's ignore that.
CREATE TABLE Ownership-- plural if you have more than one
(owner_id INTEGER NOT NULL
REFERENCES Owners(owner_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
dog_id INTEGER NOT NULL
REFERENCES Dogs(dog_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (dog_id, owner_id));
>> How can I make a query that will produce the following results: <<
Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? It is the foundation of RDBMS, after all. See why I
say you never read a book on RDBMS.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Yes, trhre are some proprietary, stinking kludges that can do this.
But do you want to be a good SQL programmer instead?
Navigation:
[Reply to this message]
|