I already tried the following queries with no success:
SELECT MainTable.recno FROM MainTable, RelatedTable WHERE ((MainTable.data1
ilike 'searchpattern') and (RelatedTable.data2 ilike 'searchpattern') and
(RelatedTable.recno=MainTable.recno));
The above query never returned any records
SELECT MainTable.recno FROM MainTable, RelatedTable WHERE ((MainTable.data1
ilike 'searchpattern') or ((RelatedTable.data2 ilike 'searchpattern') and
(RelatedTable.recno=MainTable.recno)));
The above query seemed to run for an indefinite time and eat up all RAM and
CPU time
The same happened if I used SELECT DISTINCT.
Do you have an idea what is the problem with the above queries?
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "szucs" <janos.szucs@meei.hu>
Cc: <pgsql-novice@postgresql.org>
Sent: Wednesday, September 10, 2003 3:05 PM
Subject: Re: [NOVICE] Search for data in a similar field in a related table,
too
> On Wed, Sep 10, 2003 at 11:26:27 +0200,
> szucs <janos.szucs@meei.hu> wrote:
> > I have 2 tables:
> >
> > MainTable, fields: id, data1
> > RelatedTable, fields: id, data2
> >
> > RelatedTable is joined to MainTable via the 'id' field.
> > Field 'data2' in RelatedTable holds the same sort of data as 'data1' in
> > MainTable, though they may be different in length.
> >
> > I could not figure out an SQL query, which returns all records where
either
> > 'data1' in the MainTable or 'data2' in the joined RelatedTable complies
with
> > a common search condition.
> > (For the case of simplicity I did not mention that MainTable has more
> > fields, for which separate seach conditions may apply)
> >
> > Is it possible to construct such an SQL query?
>
> Yes. Join the two tables and repeat the search condition for data1 and
> data2 using 'or' as a connector (and probably with surrounding parens).
>