Hi Richard and group,
Thanks for the alternative approach. The code is certainly cleaner and
easier to follow, but I do have a couple outer joins for fields #2 and #3
that could contain null values that are not captured in your example.
Also, I ran an 'explain query' and the performance differences were
negligable. Any further thoughts or should I just stick with what I have
and move on?
Thanks in advance.
Cheers,
Jim
>From: Richard Huxton <dev@archonet.com>
>To: T- Bone <jbowen333@hotmail.com>
>CC: pgsql-sql@postgresql.org
>Subject: Re: [SQL] Comments on subquery performance
>Date: Thu, 17 Feb 2005 19:15:14 +0000
>
>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.
>
>Well you could just do:
>
>SELECT
> l.*, c1.catname, c2.catname, c3.catname
>FROM
> tbl_listing l,
> tbl_categories c1,
> tbl_categories c2,
> tbl_categories c3
>WHERE
> l.catid1 = c1.catid
> AND l.catid2 = c2.catid
> AND l.catid3 = c3.catid
>
>--
> Richard Huxton
> Archonet Ltd
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
_________________________________________________________________
Scan and help eliminate destructive viruses from your inbound and outbound
e-mail and attachments.
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*.