Re: Complex Query - Data from 3 tables simultaneously - Mailing list pgsql-sql

From Muralidharan Ramakrishnan
Subject Re: Complex Query - Data from 3 tables simultaneously
Date
Msg-id 20051028171035.42018.qmail@web8601.mail.in.yahoo.com
Whole thread Raw
In response to Complex Query - Data from 3 tables simultaneously  (<Amit_Wadhwa@Dell.com>)
List pgsql-sql
SELECT A.SID , A.RECDATE , B.MID , B.MBDATE , C.ISSDATE FROM TableA A LEFT OUTER JOIN TableB B ON   A.SID = B.SID
LEFT OUTER JOIN TableC C ON B.MID = C.MID
ORDER BY A.SID

Amit_Wadhwa@Dell.com wrote:
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.
 
 


Enjoy this Diwali with Y! India Click here

pgsql-sql by date:

Previous
From: lucas@presserv.org
Date:
Subject: Re: Fwd: Re: Referencing
Next
From: Michael Fuhr
Date:
Subject: Re: combining records from a single table and presenting them as one record