Thread: 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.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).
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). >
On Wed, Sep 10, 2003 at 15:32:25 +0200, szucs <janos.szucs@meei.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@wolff.to> To: "szucs" <janos.szucs@meei.hu> Cc: <pgsql-novice@postgresql.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.szucs@meei.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. >