Re: Search for data in a similar field in a related table, too

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?

On Wed, Sep 10, 2003 at 11:26:27 +0200,
szucs <janos(dot)szucs(at)meei(dot)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).

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(at)wolff(dot)to>
To: "szucs" <janos(dot)szucs(at)meei(dot)hu>
Cc: <pgsql-novice(at)postgresql(dot)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(dot)szucs(at)meei(dot)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).
>

On Wed, Sep 10, 2003 at 15:32:25 +0200,
szucs <janos(dot)szucs(at)meei(dot)hu> wrote:
> 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

The above query would only match cases where data1 and data2 both matched
their search patterns. Perhaps there aren't any records for which that
happens.

> 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

It looks like you have parenthesis in the wrong places. You want the two
ors grouped together. As it is one is grouped with the and and the other
isn't. This will result in an unconstrained join which could result
in a long running query if the two tables have a lot of entries.

Thank you for your attention!

Well, I knew what were the reasons for the failures; what I did not know:
how to overcome them within the limits produced by the given syntax of
joining related tables is postgreSQL.
The same query in MSAccess, for example, which has a completely different
syntax of joining tables, looked horrible, but it could do the task, it was
relatively fast, and did not require the UNION clause which seems to be the
last resort, if any, in postgreSQL.

The trouble is caused by the fact that certain data are stored at two
places: the initial data is stored in a main MainTable field, but when an
addition to that data is necessary, it is not stored directly in that field
of the MainTable, but rather a new, related record is created in the
RelatedTable, and the addition is stored there.
That is why I have to search both tables when having a search condition for
that data: search the field containing the initial data in the MainTable and
also RelatedTable if there were any additions.
For the case to be more complicated, there can be more search conditions
regarding other fields of the MainTable and other related tables, too, which
should all be AND-ed.

----- Original Message -----
From: "Bruno Wolff III" <bruno(at)wolff(dot)to>
To: "szucs" <janos(dot)szucs(at)meei(dot)hu>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, September 15, 2003 4:23 PM
Subject: Re: [NOVICE] Search for data in a similar field in a related table,
too

> On Wed, Sep 10, 2003 at 15:32:25 +0200,
> szucs <janos(dot)szucs(at)meei(dot)hu> wrote:
> > 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
>
> The above query would only match cases where data1 and data2 both matched
> their search patterns. Perhaps there aren't any records for which that
> happens.
>
> > 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
>
> It looks like you have parenthesis in the wrong places. You want the two
> ors grouped together. As it is one is grouped with the and and the other
> isn't. This will result in an unconstrained join which could result
> in a long running query if the two tables have a lot of entries.
>