Re: nested select query failing

It's a rather nasty query format, but wrapped it to readable form.
Looks like you could make a good join from all these IN's.

Another question: does EXPLAIN (without ANALYZE) work for this query?
Could you send its output, and table defs? maybe a minimal dump in private
email?

QUESTION TO PRO'S:

Basically, is it true that IN's can be converted to RIGHT JOIN's quite
simply? Is it always worth?

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "amol" <amol(at)mithi(dot)com>
Sent: Thursday, May 15, 2003 5:57 AM

> Hi everybody,
> I am new to this mailing list, so please let me know if I am not posting
> queries the way you are expecting.
>
> - We are porting a web based application from MSSQL to postgres as a
> backend.
> This is a database intensive application. I am facing a problem in some
> queries like this :
>
> select distinct
> attached_info.id, ownerid ,attached_info.modified_date
> from attached_info
> where
> attached_info.id in
> (select distinct attached_tag_list.id from attached_tag_list
> where
> attached_tag_list.id in
> (select attached_info.id from attached_info
> where attached_info.deleted='0') and
> attached_tag_list.id in
> (select id from attached_tag_list
> where attached_tag = 262) and
> attached_tag_list.attached_tag in
> (select tags.id from tags
> where
> tags.id in
> (select tag_id
> from tag_classifier, tag_classifier_association
> where
> classifier_tag_id in
> (261, 4467, 1894, 1045, 1087, 1355, 72, 1786, 1179,
> 3090, 871, 3571, 3565, 3569, 3567, 1043, 2535, 1080,
> 3315, 87, 1041, 2343, 2345, 1869, 3088, 3872, 2651,
> 2923, 2302, 1681, 3636, 3964, 2778, 2694, 1371, 2532,
> 2527, 3742, 3740, 1761, 4530, 4671, 4503, 4512, 3700)
> and
> association_id='1566' and
> tag_classifier.uid=tag_classifier_association.uid
> ) and
> tags.isdeleted='0'
> )
> )
> order by attached_info.modified_date desc, attached_info.id desc;