Re: boolean logic expressed in RDBMS rows
Date: 12 May 2003 09:00:22 -0700
Message-ID: <8156d9ae.0305120800.596cb1f8_at_posting.google.com>
> >> My question is - is there a 'standard' way to store the query
> criteria inside a table(s) of any shape or form and what would that
> table or tables look like? <<
>
> You use a table of search values. If you want to OR them, then use a
> simple
>
> SELECT ...
> FROM Foobar
> WHERE x IN (SELECT search_x FROM SearchList);
>
> If you want to AND the search list, use a relational division. There
> is Exact Division and division with a remainder.
>
> You can play with the equality test to get a remainder division.
If you want to mix ANDing and ORing.
How about this idea?
Express each conditions by EXISTS(correlated subquery), then AND/OR each EXISTS().
(I think we can also use NOT. But I'm not shure.)
For example:
"a user w/skill = Java AND (skill = Perl OR skill = PHP)"
SELECT
FROM user u
WHERE EXISTS() AND ( EXISTS() OR EXIXTS() )
More complete statement will be like this:
SELECT u.name
FROM user u
WHERE
EXISTS( SELECT * FROM user_skill us1 , skill s1 WHERE us1.user_id = u.user_id AND s1.skill_id = us1.skill_id AND s1.name = 'Java' ) AND ( EXISTS( SELECT * FROM user_skill us2 , skill s2 WHERE us2.user_id = u.user_id AND s2.skill_id = us2.skill_id AND s2.name = 'Perl' ) OR EXISTS( SELECT * FROM user_skill us3 , skill s3 WHERE us3.user_id = u.user_id AND s3.skill_id = us3.skill_id AND s3.name = 'PHP' ) )Received on Mon May 12 2003 - 18:00:22 CEST