SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Tuesday, May 09, 2006

Line item query

SQL Apprentice Question
I have a query that I am trying to write that will get demographic
information from one table and then get journal entries from another
table. However, I want all the journal entries to be concatenated
together so in the end I only have one record for each person.

Celko Answers
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. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.


>> Each person can have only one record [sic] in Demographics. But there may be


multiple records[sic] for each person in Journal.<<

What you attempted to post makes no sense. Rows are not anything like
records, But ignoring that, if this "person_" is a key,
then BY DEFINITION there can be multiple rows per person in the Journal
table.



>> I need to take all the records [sic] from Journal, match them up to the records [sic] in Demographics and then output it all as one record [sic] per person.


If I understand what you mean, that is called violating First Normal
Form and we do not do this in an RDBMS. You want to use a 1950's COBOL
file system with variant records and an OCCURS clause.

No comments: