All,
Using Postgres 8.0 on Windows Server 2003 - 16GB Ram,
3Ghz X 2 Xeons
Accessing through JDBC / JSP
I have 3 shipment tables.
Table A - Records arrived
Shipments.
Table B - Records Materials (maybe more than one per
shipment) in the shipment.
Table C - Records Issuances of material (maybe more
than one Issuance per line item of material) in Table B.
eg.
Table A (PK = Shipment ID)
-----------
shipment
ID Recd
Date
1 2005-XX-XX
10 2005-XX-XX
Table B (PK = Material ID, FK = Shipment ID, references
Table A (shipment ID))
-----------------
shipment
ID
Material ID Material Bond Date
10
1 2005-XX-XX
10
2 2005-XX-XX
Table C (PK = Issue ID, FK = Material ID, references
Table B (Material ID))
----------------
Material
ID Issue
ID Issue
Date
1
1 2005-05-XX
1 2 2005-05-XX
I want to get data (under criteria of recvd date in
table A) the following records:
shipment
ID Recd
Date
MaterialID Bond
Date Issue
ID Issue Date
1
2005-XX-XX -
-
-
-
10 2005-XX-XX
1 2005-XX-XX 1
2005-05-XX
10 2005-XX-XX
1 2005-XX-XX 2
2005-05-XX
10 2005-XX-XX 2 2005-XX-XX -
-
Basically I want a raw dump of data
- Should have all the shipments regardless of whether
they have any material items entered or not
- Should have all Material Items for Every Shipment
regardless of whether it was issued or not.
I know I need an outer join (Do I Not?), but am
confused as to how to implement it.
Because this seems to be a requirement of a reversed
outer join (??)
Please assist,
Thanks in advance.
