pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedComplex Query - Data from 3 tables simultaneously
5+ messages / 3 participants
[nested] [flat]
* Complex Query - Data from 3 tables simultaneously
@ 2005-10-27 07:37 Amit_Wadhwa@Dell.com
0 siblings, 2 replies; 5+ messages in thread
From: Amit_Wadhwa@Dell.com @ 2005-10-27 07:37 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Complex Query - Data from 3 tables simultaneously
@ 2005-10-27 09:17 Richard Huxton <dev@archonet.com>
parent: Amit_Wadhwa@Dell.com
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-10-27 09:17 UTC (permalink / raw)
To: Amit_Wadhwa@Dell.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Complex Query - Data from 3 tables simultaneously
@ 2005-10-27 11:23 Amit_Wadhwa@Dell.com
0 siblings, 0 replies; 5+ messages in thread
From: Amit_Wadhwa@Dell.com @ 2005-10-27 11:23 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Complex Query - Data from 3 tables simultaneously
@ 2005-10-28 17:10 Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
parent: Amit_Wadhwa@Dell.com
1 sibling, 0 replies; 5+ messages in thread
From: Muralidharan Ramakrishnan @ 2005-10-28 17:10 UTC (permalink / raw)
To: Amit_Wadhwa@Dell.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Complex Query - Data from 3 tables simultaneously
@ 2005-10-29 05:45 Amit_Wadhwa@Dell.com
0 siblings, 0 replies; 5+ messages in thread
From: Amit_Wadhwa@Dell.com @ 2005-10-29 05:45 UTC (permalink / raw)
To: contact2muraliin@yahoo.co.in; pgsql-sql
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@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:
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-29 05:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-27 07:37 Complex Query - Data from 3 tables simultaneously Amit_Wadhwa@Dell.com
2005-10-27 09:17 ` Richard Huxton <dev@archonet.com>
2005-10-28 17:10 ` Muralidharan Ramakrishnan <contact2muraliin@yahoo.co.in>
2005-10-27 11:23 Re: Complex Query - Data from 3 tables simultaneously Amit_Wadhwa@Dell.com
2005-10-29 05:45 Re: Complex Query - Data from 3 tables simultaneously Amit_Wadhwa@Dell.com
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