m:n relation: AND operator on same field

From: Dirk P.C. Wendt <DirkPCWendt_at_t-online.de>
Date: Fri, 22 Feb 2002 01:42:12 +0100
Message-ID: <a54457$4j0dl$1@ID-25048.news.dfncis.de>

Hello,

I am programming an online-search-form where people can define and submit their queries with different logical and relational operators -- huuuh.

Any hints are greatly appreciated.

1. searching on m:n relations with and operator on same field Due to the usage of AND in the same field on tables in n:m-relations I've searched for a "solution". I am not sure if this is common practice.

For each searchable field (m:n) I've defined a subquery which is build into the "main" query with an EXISTS clause, surrounded by parantheses. The subqueries (n = number of search terms) are conjuncted by a UNION for OR and an INTERSECT for AND. Due to the usage of Oracle 8.1.7 the search string is going through a function which sets parantheses to secure the correct precedence between union and intersect.

search term z: 1 or 2 and 3

select xyz from

     ( inline_view , rownum hlpsort from
           ( inline_view with order_clause) table
   where .....
       and exists


               .... z = 1
              union




                 ..... z = 2

              intersect


                 ..... z = 3

               )
           )
       and rownum < n
     )

where hlpsort > m;

Thank you very much.

Regards,
Dirk. Received on Thu Feb 21 2002 - 18:42:12 CST