Complex Query - Data from 3 tables simultaneously - Mailing list pgsql-sql
| From | |
|---|---|
| Subject | Complex Query - Data from 3 tables simultaneously |
| Date | |
| Msg-id | 4538E1203665624F97A8494BCF2716E5F4853F@blrx2kmbgl102.blr.amer.dell.com Whole thread Raw |
| Responses |
Re: Complex Query - Data from 3 tables simultaneously
(Richard Huxton <dev@archonet.com>)
Re: Complex Query - Data from 3 tables simultaneously (Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>) |
| List | pgsql-sql |
<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>