Re: joins
From: Joe Celko (jcelko212_at_earthlink.net)
Date: 10/19/04
- Next message: Marina S.: "Using macro for multiple insert statements into sql server"
- Previous message: Nuno Silva: "Subquerie in CHECK Constrain"
- In reply to: Elisabeth: "joins"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 19 Oct 2004 09:20:57 -0700
Please read a book on data modeling and the ISO-11179 Standards.
There is no such thing as "type" - type is a modifier of something. You
do not understand what an identifier is, so you stick it on the end of
every key in your schema.
Why do you have so many VARCHAR(50) columns? Bet that you are writing in
ACCESS and got lazy. Using "magical numbers" is a good sign of a lack
of any kind of design research or a data dictionary. If you give that
much space for a name, you will eventually get a name that long.
Your FK syntax was wrong and the standard syntax is INSERT INTO. Why
all the NULLs? Let's do a clean up on schema first.
CREATE TABLE Products
(prod_id INTEGER NOT NULL PRIMARY KEY,
prod_name VARCHAR(20));
INSERT INTO products VALUES(1, 'car');
INSERT INTO Products VALUES(2, 'bike');
INSERT INTO Products VALUES(3, 'van');
CREATE TABLE ProductTypes
(prod_type INTEGER NOT NULL PRIMARY KEY,
prod_type_desc VARCHAR(20) NOT NULL);
INSERT INTO ProductTypes VALUES(1, 'a');
INSERT INTO ProductTypes VALUES(2, 'b');
A name like "ProductPoints" makes no sense at all. How about a
"type_value_id_name" while we are stringing adjectives together?
It also looks like your original primary key was a sequential number and
not a key at all. I have to rename this nightmare of buzz words --
well, "buzz-affixes": I made the vague reserved word "value" into
"points":
CREATE TABLE ProductPoints
(prod_id INTEGER NOT NULL
REFERENCES Products(product_id),
prod_type INTEGER NOT NULL
REFERENCES ProductTypes(prod_type),
prod_points INTEGER NOT NULL,
PRIMARY KEY (product_id, prod_type));
INSERT INTO ProductPoints VALUES (1, 1, 3);
INSERT INTO ProductPoints VALUES (1, 2, 1);
INSERT INTO ProductPoints VALUES (2, 1, 3);
INSERT INTO ProductPoints VALUES (3, 1, 3);
INSERT INTO ProductPoints VALUES (3, 2, 44);
Try this version:
SELECT P.prod_name, T.prod_type, V.prod_points
FROM (Products AS P
CROSS JOIN
ProductTypes AS T)
LEFT OUTER JOIN
ProductPoints AS V
ON V.prod_id = P.prod_id
AND V.prod_type = T.prod_type;
You get all possible products and product types, then do the outer join
to see if there are points for that combination. Try to use the LEFT
OUTER JOIN in a culture that reads left to right to help the guy
maintaining the code.
And before I forget, thank for the DDL; so many newbies are not
bothering with it. Not a bad first shot at a schema, but you need that
data modeling book. You'd get a B or B+ :)
--CELKO--
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.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Marina S.: "Using macro for multiple insert statements into sql server"
- Previous message: Nuno Silva: "Subquerie in CHECK Constrain"
- In reply to: Elisabeth: "joins"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
