X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A11A1DB254 for ; Fri, 28 Oct 2005 14:10:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40335-02 for ; Fri, 28 Oct 2005 17:10:42 +0000 (GMT) Received: from web8601.mail.in.yahoo.com (web8601.mail.in.yahoo.com [202.43.219.76]) by svr1.postgresql.org (Postfix) with SMTP id D6856DB247 for ; Fri, 28 Oct 2005 14:10:40 -0300 (ADT) Received: (qmail 42020 invoked by uid 60001); 28 Oct 2005 17:10:35 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.in; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=XXXuXMFsBIBBN5Hnu+i1ufNQzXAbT/iq37FJPk89OO/HzC0lI+9URnnloDZ2ztVkjc2Q1CJ6nCynEAg2MeSZua6nc0ycxshs6GvE8+EnBuu3NaHwJVjp6AftBCG/tib0Uh+VQUjm6tp6JR3NXuBdOB7rwMCsY5N4rIleITd9LV4= ; Message-ID: <20051028171035.42018.qmail@web8601.mail.in.yahoo.com> Received: from [12.109.150.2] by web8601.mail.in.yahoo.com via HTTP; Fri, 28 Oct 2005 18:10:35 BST Date: Fri, 28 Oct 2005 18:10:35 +0100 (BST) From: Muralidharan Ramakrishnan Subject: Re: Complex Query - Data from 3 tables simultaneously To: Amit_Wadhwa@Dell.com, pgsql-sql@postgresql.org In-Reply-To: <4538E1203665624F97A8494BCF2716E5F4853F@blrx2kmbgl102.blr.amer.dell.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1499511091-1130519435=:42004" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.468 required=5 tests=[AWL=-0.452, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, HTML_MESSAGE=0.001] X-Spam-Score: 1.468 X-Spam-Level: * X-Archive-Number: 200510/387 X-Sequence-Number: 23240 --0-1499511091-1130519435=:42004 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 --0-1499511091-1130519435=:42004 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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 --0-1499511091-1130519435=:42004--