[thelist] SQL view construction
Christopher Joseph
christopher at ideadesigners.com
Fri Sep 26 04:51:16 CDT 2003
Hi Guys,
Trying to build a view over four tables is causing MAJOR headaches. The
relationships between the tables is as follows:
File 1:invoice headers
--------------
PK invoice number
other stuff
File 2:invoice details
---------------
other stuff
FK invoice number
File 3:archived invoice headers
--------------
PK invoice number
other stuff
File 4:archived invoice details
---------------
other stuff
FK invoice number
We initially tried to contruct two views - one for all headers and one
for all details but we couldn't seem to get the the views to work
because the column names are the same in both sets of related files i.e.
we want to merge identical columns *not* list both e.g.
YES this view -> Aggregate Header View
PK invoice number | other stuff
---------------------------------------
File 1:invoice number | File 1:other stuff
File 3:invoice number | File 3:other stuff
File 1:invoice number | File 1:other stuff
File 1:invoice number | File 1:other stuff
File 3:invoice number | File 3:other stuff
NOT this view -> Aggregate Header View
PK invoice number | alias(invoice number)
----------------------------------------------etc.
File 1:invoice number | null
null | File 3:invoice number
File 1:invoice number | null
File 1:invoice number | null
null | File 3:other stuff
Any thoughts on how to solve this and if I create two views can I create
a third over the top of both?
--
Christopher Joseph
-------------------
[Internet]
http://www.ideadesigners.com [iseries & web technologies]
mailto: christopher at ideadesigners.com
[Telephone]
mobile: +44 7966 003860
Office: +44 1494 731814 ext. 832
Direct: +44 1494 731832
[Instant Message]
ICQ: 78019724
YIM = Josephc_98
MSN = christopher at ideadesigners.com
More information about the thelist
mailing list