Subject RE: [ib-support] subquery problem
Author Bogdan Mugerli
I was too fast in logical operators

how about this:

select t1."docid", t1."Name", t1."test" from Table T1
Inner join Table T2 on t2."docid" = T1."docid"
where t1."Name" = t2."Name" and
(t1."Name" = :Name1 or t1."Name" = :Name2)

It's a lot faster


-----Original Message-----
From: Svein Erling Tysvaer [mailto:svein.erling.tysvaer@...]
Sent: Thursday, March 20, 2003 9:43 AM
Subject: RE: [ib-support] subquery problem

Unfortunately, I don't think this works, Bogdan. This will return all rows
with name1 or name3, since the selfjoin can be to the same row (e.g. if
'name1' has a docid of 3, it will match itself whether or not 'name3' has a
docid of 3). What I think German wants is something like

select <columns> from <Table> T1
where exists(
select 1 from <Table> T2
join <Table> T3 on T3.docid = T2.docid
join <Table> T4 on T4.docid = T2.docid
where T1.docid = t2.docid
and = :name1
and = :name2
and = :name3)
and T1.Name IN (:name1, :name2, :name3)

I made this example with three values, since I think it is easier to see
what has to be done to extend or limit the select to cover for more/less
names to match. I don't think this can be solved in a general way covering
a random number of names to be matched, but that a separate query has to be
written for each number. But of course, I hope I am wrong.