pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan I do this smarter?
5+ messages / 3 participants
[nested] [flat]
* Can I do this smarter?
@ 2006-07-14 03:41 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 5+ messages in thread
From: Joost Kraaijeveld @ 2006-07-14 03:41 UTC (permalink / raw)
To: pgsql-sql
I have three tables: customers, salesorders and invoices. Customers have
salesorders and salesorders have invoices ( child tables have foreign
key columns to their parent).
I want to get a list of all invoices with their customers. This what I
came up with:
select
invoices.objectid,
invoices.invoicenumber,
invoices.invoicedate,
(select customer from salesorders where objectid = invoices.salesorderobjectid),
(select customernumber from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid)),
(select lastname from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid))
from invoices
Can I do this smarter as the three subselects select the same customer three times and I would think that 1 time is enough?
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can I do this smarter?
@ 2006-07-14 03:52 Aaron Bono <postgresql@aranya.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 2 replies; 5+ messages in thread
From: Aaron Bono @ 2006-07-14 03:52 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
On 7/13/06, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote:
>
> I have three tables: customers, salesorders and invoices. Customers have
> salesorders and salesorders have invoices ( child tables have foreign
> key columns to their parent).
>
> I want to get a list of all invoices with their customers. This what I
> came up with:
>
> select
> invoices.objectid,
> invoices.invoicenumber,
> invoices.invoicedate,
> (select customer from salesorders where objectid =
> invoices.salesorderobjectid),
> (select customernumber from customers where objectid = (select customer
> from salesorders where objectid = invoices.salesorderobjectid)),
> (select lastname from customers where objectid = (select customer from
> salesorders where objectid = invoices.salesorderobjectid))
> from invoices
>
> Can I do this smarter as the three subselects select the same customer
> three times and I would think that 1 time is enough?
SELECT
invoices.objectid,
invoices.invoicenumber,
invoices.invoicedate,
salesorders.customer,
customers.customernumber,
customers.lastname
FROM invoices
INNER JOIN salesorders ON (
salesorders.objectid = invoices.salesorderobjectid
)
INNER JOIN customers ON (
customers.objectid = salesorder.customer
)
You should do INNER and OUTER joins for connecting the tables by their
foreign keys.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can I do this smarter?
@ 2006-07-14 04:16 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 5+ messages in thread
From: Joost Kraaijeveld @ 2006-07-14 04:16 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
Hi Aaron,
On Thu, 2006-07-13 at 22:52 -0500, Aaron Bono wrote:
> SELECT
> invoices.objectid,
> invoices.invoicenumber,
> invoices.invoicedate,
> salesorders.customer,
> customers.customernumber,
> customers.lastname
> FROM invoices
> INNER JOIN salesorders ON (
> salesorders.objectid = invoices.salesorderobjectid
> )
> INNER JOIN customers ON (
> customers.objectid = salesorder.customer
> )
>
> You should do INNER and OUTER joins for connecting the tables by
> their foreign keys.
Thanks for the quick (and working ;-)) response.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can I do this smarter?
@ 2006-07-14 14:07 Aaron Bingham <bingham@cenix-bioscience.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 5+ messages in thread
From: Aaron Bingham @ 2006-07-14 14:07 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; pgsql-sql
Aaron Bono wrote:
> On 7/13/06, *Joost Kraaijeveld* <J.Kraaijeveld@askesis.nl
> <mailto:J.Kraaijeveld@askesis.nl>> wrote:
>
> I have three tables: customers, salesorders and invoices.
> Customers have
> salesorders and salesorders have invoices ( child tables have foreign
> key columns to their parent).
>
> I want to get a list of all invoices with their customers. This
> what I
> came up with:
>
> select
> invoices.objectid,
> invoices.invoicenumber,
> invoices.invoicedate,
> (select customer from salesorders where objectid =
> invoices.salesorderobjectid),
> (select customernumber from customers where objectid = (select
> customer from salesorders where objectid =
> invoices.salesorderobjectid)),
> (select lastname from customers where objectid = (select customer
> from salesorders where objectid = invoices.salesorderobjectid))
> from invoices
>
> Can I do this smarter as the three subselects select the same
> customer three times and I would think that 1 time is enough?
>
>
>
> SELECT
> invoices.objectid,
> invoices.invoicenumber,
> invoices.invoicedate,
> salesorders.customer,
> customers.customernumber,
> customers.lastname
> FROM invoices
> INNER JOIN salesorders ON (
> salesorders.objectid = invoices.salesorderobjectid
> )
> INNER JOIN customers ON (
> customers.objectid = salesorder.customer
> )
>
> You should do INNER and OUTER joins for connecting the tables by
> their foreign keys.
You can also rewirite this (IMO) more clearly thus:
SELECT
invoices.objectid,
invoices.invoicenumber,
invoices.invoicedate,
salesorders.customer,
customers.customernumber,
customers.lastname
FROM invoices, salesorders, customers
WHERE salesorders.objectid = invoices.salesorderobjectid
AND customers.objectid = salesorder.customer;
--
--------------------------------------------------------------------
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Can I do this smarter?
@ 2006-07-14 14:21 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Aaron Bingham <bingham@cenix-bioscience.com>
0 siblings, 0 replies; 5+ messages in thread
From: Joost Kraaijeveld @ 2006-07-14 14:21 UTC (permalink / raw)
To: Aaron Bingham <bingham@cenix-bioscience.com>; +Cc: Aaron Bono <postgresql@aranya.com>; pgsql-sql
This is clearly the "Aaron helps day" ;-)
Thanks,
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-14 14:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-14 03:41 Can I do this smarter? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-07-14 03:52 ` Aaron Bono <postgresql@aranya.com>
2006-07-14 04:16 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-07-14 14:07 ` Aaron Bingham <bingham@cenix-bioscience.com>
2006-07-14 14:21 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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