From: Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
To: Amit_Wadhwa@Dell.com
To: pgsql-sql@postgresql.org
Subject: Re: Complex Query - Data from 3 tables simultaneously
Date: Fri, 28 Oct 2005 18:10:35 +0100 (BST)
Message-ID: <20051028171035.42018.qmail@web8601.mail.in.yahoo.com> (raw)
In-Reply-To: <4538E1203665624F97A8494BCF2716E5F4853F@blrx2kmbgl102.blr.amer.dell.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: contact2muraliin@yahoo.co.in, Amit_Wadhwa@Dell.com
Subject: Re: Complex Query - Data from 3 tables simultaneously
In-Reply-To: <20051028171035.42018.qmail@web8601.mail.in.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox