Subject RE: [ib-support] subquery problem
Author Svein Erling Tysvaer
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.


At 16:36 19.03.2003 +0100, you wrote:
>now i think it will work: (:-)
>select * from Table T1
>inner join Table T2 on t2.docid = T1.docid
>where ( = 'name1' or = 'name3') and ( = 'name1' or
> = 'name3'