Обсуждение: Complex Query - Data from 3 tables simultaneously
<div align="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">All,</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005"><div align="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005"><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005">Using Postgres 8.0 on Windows Server 2003 - 16GB Ram, 3Ghz X 2 Xeons</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">Accessingthrough JDBC / JSP</span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005"></span></font> </div></span></font></div></span></font></div><divalign="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005">I have 3 shipment tables.</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">TableA - Records arrived Shipments.</span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005">Table B - Records Materials (maybe more than one pershipment) in the shipment.</span></font></div><div align="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005">Table C - Records Issuances of material (maybe more than one Issuance per line itemof material) in Table B.</span></font></div><div align="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005"></span></font> </div><div align="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">eg. </span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005">Table A (PK = Shipment ID)</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">-----------</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005">shipment ID Recd Date</span></font></div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005">1 2005-XX-XX</span></font></div><divalign="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005">10 2005-XX-XX</span></font></div><divalign="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><div align="left"dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">Table B (PK = MaterialID, FK = Shipment ID, references Table A (shipment ID))</span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005">-----------------</span></font></div><div align="left"dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">shipment ID Material ID Material Bond Date</span></font></div><div align="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">10 1 2005-XX-XX</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005">10 2 2005-XX-XX</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005"></span></font> </div><div align="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">Table C (PK = Issue ID, FK = Material ID, references Table B (MaterialID))</span></font></div><div align="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">----------------</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">Material ID Issue ID Issue Date</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005"><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">1 1 2005-05-XX <div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005">1 2 2005-05-XX</span></font></div></span></font></div></span></font><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005"></span></font></div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><div align="left"dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">I want to get data (undercriteria of recvd date in table A) the following records:</span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005">shipment ID Recd Date MaterialID Bond Date Issue ID Issue Date</span></font></div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><div align="left"dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">1 2005-XX-XX - - - -</span></font></div><div align="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">10 2005-XX-XX 1 2005-XX-XX 1 2005-05-XX</span></font></div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005"><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005">10 2005-XX-XX 1 2005-XX-XX 2 2005-05-XX <div align="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">10 2005-XX-XX 2 2005-XX-XX - -</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005"></span></font> </div><div align="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">Basically I want a raw dump of data</span></font></div><div align="left"dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005">- Should have all theshipments regardless of whether they have any material items entered or not</span></font></div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005">- Should have all Material Items forEvery Shipment regardless of whether it was issued or not.</span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><div align="left" dir="ltr"><fontcolor="#0000ff" face="Arial" size="2"><span class="843412607-27102005">I know I need an outer join (Do I Not?),but am confused as to how to implement it.</span></font></div><div align="left" dir="ltr"><font color="#0000ff" face="Arial"size="2"><span class="843412607-27102005">Because this seems to be a requirement of a reversed outer join (??)</span></font></div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005">Please assist, </span></font></div><div align="left" dir="ltr"><font color="#0000ff"face="Arial" size="2"><span class="843412607-27102005">Thanks in advance.</span></font></div><div align="left"dir="ltr"><font color="#0000ff" face="Arial" size="2"><span class="843412607-27102005"></span></font> </div><divalign="left" dir="ltr"><font color="#0000ff" face="Arial" size="2"><spanclass="843412607-27102005"></span></font> </div></span></font></div></span></font></div>
Amit_Wadhwa@Dell.com wrote: > > 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 (??) SELECT s.*, m.*, i.* FROM shipments s LEFT JOIN materials m ON s.www = m.xxx LEFT JOIN issued i ON m.yyy = i.zzz WHERE s.whatever = something -- Richard Huxton Archonet Ltd
Thanks a lot, that worked for me! -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Thursday, October 27, 2005 2:47 PM To: Wadhwa, Amit Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Complex Query - Data from 3 tables simultaneously Amit_Wadhwa@Dell.com wrote: > > 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 (??) SELECT s.*, m.*, i.* FROM shipments s LEFT JOIN materials m ON s.www = m.xxx LEFT JOIN issued i ON m.yyy = i.zzz WHERE s.whatever = something -- Richard Huxton Archonet Ltd
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:
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
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
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@yahoo.co.in]
Sent: Friday, October 28, 2005 10:41 PM
To: Wadhwa, Amit; pgsql-sql@postgresql.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@Dell.com wrote:
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