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 E20619FB35B for ; Fri, 14 Jul 2006 11:36:34 -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 24185-09 for ; Fri, 14 Jul 2006 11:36:29 -0300 (ADT) X-Greylist: delayed 00:28:39.831881 by SQLgrey- Received: from mxsrv01.cenix-bioscience.com (exon.cenix-bioscience.com [62.80.26.100]) by postgresql.org (Postfix) with ESMTP id B98E49FB334 for ; Fri, 14 Jul 2006 11:36:29 -0300 (ADT) Received: by mxsrv01.cenix-bioscience.com (Cenix-Mailer, from userid 65534) id 2F6C71AA3E5; Fri, 14 Jul 2006 16:07:42 +0200 (CEST) Received: from mxsrv01.cenix-bioscience.com (localhost [127.0.0.1]) by mxsrv01-sf.cenix-bioscience.com (Cenix-Mailer) with ESMTP id 97C691A458A; Fri, 14 Jul 2006 16:07:41 +0200 (CEST) Received: from [192.168.1.104] (unknown [192.168.1.104]) by mxsrv01.cenix-bioscience.com (Cenix-Mailer) with ESMTP id 2DC14197911; Fri, 14 Jul 2006 16:07:37 +0200 (CEST) Message-ID: <44B7A529.6090201@cenix-bioscience.com> Date: Fri, 14 Jul 2006 16:07:37 +0200 From: Aaron Bingham User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Aaron Bono Cc: Joost Kraaijeveld , pgsql-sql@postgresql.org Subject: Re: Can I do this smarter? References: <1152848488.6045.13.camel@localhost> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/211 X-Sequence-Number: 25785 Aaron Bono wrote: > On 7/13/06, *Joost Kraaijeveld* > 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 --------------------------------------------------------------------