X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id D800D9FB2DB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 14 Jul 2006 00:52:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 60403-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 14 Jul 2006 00:52:41 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.185])
	by postgresql.org (Postfix) with ESMTP id 687739FB2D5
	for <pgsql-sql@postgresql.org>; Fri, 14 Jul 2006 00:52:41 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id b2so73380nfe
	for <pgsql-sql@postgresql.org>; Thu, 13 Jul 2006 20:52:39 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=KZa6UEzuEgi0uSJlV7CtdOw5isy5NU31Bzk/Wtl5oay3izM1riAAPGkYveX8QX/IAm6YjVvtjpYkwognxx9XJeNg7/DmFu3EctBOlR05xPzbDQancneVMBv15r1e//GzOr6BKYA3k2Hr/otC3ZIxfFUYd1q+VgRvoNcLszc2OgU=
Received: by 10.78.164.13 with SMTP id m13mr1436249hue;
	Thu, 13 Jul 2006 20:52:39 -0700 (PDT)
Received: by 10.78.29.15 with HTTP; Thu, 13 Jul 2006 20:52:39 -0700 (PDT)
Message-ID: <bf05e51c0607132052l6b95d5b1m67d5620e80e7491e@mail.gmail.com>
Date: Thu, 13 Jul 2006 22:52:39 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Joost Kraaijeveld" <J.Kraaijeveld@askesis.nl>
Subject: Re: Can I do this smarter?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1152848488.6045.13.camel@localhost>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_22959_9177519.1152849159202"
References: <1152848488.6045.13.camel@localhost>
X-Google-Sender-Auth: 039515178646ea1b
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/208
X-Sequence-Number: 25782

------=_Part_22959_9177519.1152849159202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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
==================================================================

------=_Part_22959_9177519.1152849159202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/13/06, <b class="gmail_sendername">Joost Kraaijeveld</b> &lt;<a href="mailto:J.Kraaijeveld@askesis.nl">J.Kraaijeveld@askesis.nl</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I have three tables: customers, salesorders and invoices. Customers have<br>salesorders and salesorders have invoices ( child tables have foreign<br>key columns to their parent).<br><br>I want to get a list of all invoices with their customers. This what I
<br>came up with:<br><br>select<br>invoices.objectid,<br>invoices.invoicenumber,<br>invoices.invoicedate,<br>(select customer from salesorders where objectid = invoices.salesorderobjectid),<br>(select customernumber from customers where objectid = (select customer from salesorders where objectid = 
invoices.salesorderobjectid)),<br>(select lastname from customers where objectid = (select customer from salesorders where objectid = invoices.salesorderobjectid))<br>from invoices<br><br>Can I do this smarter as the three subselects select the same customer three times and I would think that 1 time is enough?
</blockquote><div><br>&nbsp;</div>SELECT<br>&nbsp;&nbsp; invoices.objectid,<br>&nbsp;&nbsp; invoices.invoicenumber,<br>&nbsp;&nbsp; invoices.invoicedate,<br>&nbsp;&nbsp; salesorders.customer,<br>&nbsp;&nbsp; customers.customernumber,<br>&nbsp;&nbsp; customers.lastname<br>FROM invoices<br>
INNER JOIN salesorders ON (<br>&nbsp;&nbsp; salesorders.objectid = invoices.salesorderobjectid<br>)<br>INNER JOIN customers ON (<br>&nbsp;&nbsp; customers.objectid = salesorder.customer<br>)<br><br>You should do INNER and OUTER&nbsp; joins for connecting the tables by their foreign keys.
<br></div><br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com</a><br>==================================================================

------=_Part_22959_9177519.1152849159202--