Re: [SQL] Complex Query - Data from 3 tables simultaneously

[Muralidharan Ramakrishnan]

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

[EMAIL PROTECTED] 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