From: Edmund Bacon <ebacon@onesystem.com>
To: T- Bone <jbowen333@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Comments on subquery performance
Date: Wed, 23 Feb 2005 09:05:24 -0700
Message-ID: <421CA9C4.4050506@onesystem.com> (raw)
In-Reply-To: <BAY103-F17FF599D1FD15D1E06780BE76D0@phx.gbl>
References: <BAY103-F17FF599D1FD15D1E06780BE76D0@phx.gbl>
Typically, this is handled as (omitting '"'s and schema names for clarity)
SELECT CatID1, CatID2, CatID3, t1.CatName as CatName1,
t2.CatName as CatName2, t3.CatName as CatName3
FROM tblListings
JOIN tblCategories t1 on CatId1 = CatID
JOIN tblCategories t2 on CatId2 = CatID
JOIN tblCategories t3 on CatId3 = CatID;
Wether you need to LEFT JOIN tblCategories or not depends on how you
want to do if CatID1, CatID2, or CatID3 is NULL.
T- Bone wrote:
> (second attempt in two days to post this message...I appologise if for
> some reason a duplicate appears)
>
> Hello all,
>
> I created a query that contains two subqueries and joins and would
> like some feedback on whether:
> 1) this approach is logical; and,
> 2) if this is an optimal approach (performance wise) to return the
> records I seek.
>
> Essentially, I am attempting to perform a 'lookup' on a value in
> another table 3 times, for three different columns. I have three
> columns with category codes in tblListings and would like to 'lookup'
> the actual category text in tblCategory. I have created a functional
> query that contains two subqueries and joins, but am concerned this
> may not be the fastest (or logical?) way to achieve what I seek.
>
> I thought of another approach to create a function to evaluate the
> records on a row-by-row and column-by-column approach, but thought
> that may prove even slower. I would appreciate any comments on my
> logic or learning of any alternative means that would result in better
> performance.
>
> I have included the SQL for reference. Thanks in advance.
>
> Regards,
> Jim
>
> ----------------------------------------8<-------------------------------------------------
>
>
> SELECT "CatID1", "CatID2", "CatID3", c1 AS "CatName1", c2 AS
> "CatName2", t6."CatName" AS "CatName3"
> FROM
> (SELECT "CatID1", "CatID2", "CatID3", c1, t4."CatName" AS c2
> FROM
> (SELECT t1."CatID1", t1."CatID2", t1."CatID3", t2."CatName" AS c1
> FROM "MySchema"."tblListings" t1
> INNER JOIN "MySchema"."tblCategories" t2
> ON (t1."CatID1" = t2."CatID")) t3
> LEFT OUTER JOIN "MySchema"."tblCategories" t4
> ON (t3."CatID2" = t4."CatID")) t5
> LEFT OUTER JOIN "MySchema"."tblCategories" t6
> ON (t5."CatID3" = t6."CatID");
>
> ----------------------------------------8<-------------------------------------------------
>
>
> _________________________________________________________________
> Take advantage of powerful junk e-mail filters built on patented
> Microsoft® SmartScreen Technology.
> http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotm...
> Start enjoying all the benefits of MSN® Premium right now and get the
> first two months FREE*.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Edmund Bacon <ebacon@onesystem.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ebacon@onesystem.com, jbowen333@hotmail.com
Subject: Re: Comments on subquery performance
In-Reply-To: <421CA9C4.4050506@onesystem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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