Re: Comments on subquery performance - Mailing list pgsql-sql

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://hotmail.com/enca&HL=Market_MSNIS_Taglines

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