On Tue, Aug 22, 2000 at 01:50:26PM +0400, Alex Guryanow wrote:
> Hi,
>
> postgresql 7.0.2. Why by executing the following query
>
> select * from magazine
> where id in (
> select mag_id from dict where word = 'akademie' intersect
> select mag_id from dict where word = 'der' intersect
> select mag_id from dict where word = 'klasse' )
>
> I receive the following error:
>
> ERROR: parse error at or near 'intersect'
[snip]
>
> Is it possible to use INTERSECT and UNION keywords in subqueries?
I guess not. I imagine this limitation will be lifted in a future version.
An alternative possibility is joining the table to itself on mag_id:
select * from magazine
where id in (
select d1.mag_id from dict as d1, dict as d2, dict as d3
where d1.word = 'akademie' and d2.word='der'
and d3.word='klasse'
and d1.mag_id = d2.mag_id and d2.mag_id = d3.mag_id)
In fact, do the whole thing as one big join:
select * from magazine,dict as d1, dict as d2, dict as d3
where d1.word = 'akademie' and d2.word='der'
and d3.word='klasse'
and d1.mag_id = d2.mag_id and d2.mag_id = d3.mag_id
and magazine.id = d1.mag_id;
This should work optimally if you have indexes on
magazine(id)
dict(mag_id)
dict(word)
Hope that helps,
Jules
