[thelist] SQL view construction

[Christopher Joseph]

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