Re: Complex Query - Data from 3 tables simultaneously

SELECT a.*,b.*,c.*,c.issuedate-a.recd_date as age FROM shipments a LEFT
JOIN materials b ON a.shipid = b.shipid;
LEFT JOIN issuetable c ON b.material_id = c.material_id
WHERE (a.recd_date between cast(' "+date1+" 00:00:00' as datetime) and
cast(' "+date2 + ' ") 23:59:59' as datetime)

Did the above, got the expected results, did not specify 'Outer Join'
only specified Join, is that a problem?

_____

From: Muralidharan Ramakrishnan [mailto:contact2muraliin(at)yahoo(dot)co(dot)in]
Sent: Friday, October 28, 2005 10:41 PM
To: Wadhwa, Amit; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Complex Query - Data from 3 tables simultaneously

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(at)Dell(dot)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
<http://in.promos.yahoo.com/fabmall/index.html>