Unexpected Join behavior
in [Programming SQL]
|
From: Mij on 15 Jul 2010 15:59 I see you are right about the orig row being there even when the follow-ups are not, I must have been looking at some other configuration. Alright, I think I understand what you are saying about the left join a little better. You suggest a good alternative design; the main reason we didn't do that when the database was created was that the inspection had 22 columns of data, where the follow-up inspections had 7 columns of data (not really any new ones though) and we had some notion of records with lots of nulls. However, that may be something to consider in a future version of their system. Thanks for your explanation. Mia J. *** Sent via Developersdex http://www.developersdex.com ***
From: Mij on 15 Jul 2010 16:08 Tom, Thanks for your explanation also. I'll keep in mind the date thing. Mia J. *** Sent via Developersdex http://www.developersdex.com ***
From: --CELKO-- on 15 Jul 2010 16:47 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. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html Here is my guess at what you did not tell us with your narrative: CREATE TABLE Inspections (inspection_id INTEGER NOT NULL PRIMARY KEY, property_id INTEGER NOT NULL REFERENCES Properties(property_id), inspection_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, occupied_flg CHAR(1) NOT NULL, CHECK (occupied_flg IN ('y', 'n')) ); CREATE TABLE FollowUpInspections (inspection_id INTEGER NOT NULL REFERENCES Inspections(inspection_id), followup_id INTEGER NOT NULL, PRIMARY KEY(inspection_id, followup_id) followup_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL); It looks like you have inspections in TWO places when they should in one place. Get that initial visit where it belongs: CREATE TABLE Inspections (inspection_id INTEGER NOT NULL PRIMARY KEY, property_id INTEGER NOT NULL REFERENCES Properties(property_id), ..); CREATE TABLE FollowUps (inspection_id INTEGER NOT NULL REFERENCES Inspections(inspection_id), visit_nbr INTEGER NOT NULL CHECK (visit_nbr >= 0), PRIMARY KEY(inspection_id, followup_id) visit_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, occupied_flg CHAR(1) NOT NULL, CHECK (occupied_flg IN ('y', 'n')) Use zero for initial visit, if that makes sense. Bad DDL leads to worse DML.
From: Mij on 15 Jul 2010 17:04 I hadn't considered it that way, but that's definitely something to think about. Thank you for your comments. Mia J. *** Sent via Developersdex http://www.developersdex.com ***
From: Mij on 15 Jul 2010 17:09
I hadn't considered it that way, but that's definitely something to think about. Thank you for your comments. Mia J. *** Sent via Developersdex http://www.developersdex.com *** |