From: | "szucs" <janos(dot)szucs(at)meei(dot)hu> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Search for data in a similar field in a related table, too |
Date: | 2003-09-10 13:32:25 |
Message-ID: | 000601c3779f$f9198310$8e00a8c0@meei.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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).
>
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-09-10 14:28:43 | Re: Which files belong to which database? |
Previous Message | Jason Hihn | 2003-09-10 13:12:58 | Re: Lyris and Postgres |