CODEWITHSUNDEEP
mysqlsqltuplestuple-relational-calculus
user1613360
user1613360

Reputation: 1314

How to implement sql "where not in" in tuple relational calculus?

I want to convert a sql query which has where not in clause to tuple relational calculus.Existential and Universal quantifier implements only where exists and where not exists clause so I want to know how to implement where not in?

My tables are serves(bar,beer),frequents(drinker,bar),likes(drinker,beer).The following query selects the drinkers that frequent only bars that serve some beer they like.

select distinct f2.drinker from frequents f2 where f2.drinker not in (select f1.drinker from frequents f1 where (f1.bar,f1.drinker) not in (select f.bar,f.drinker from frequents f,serves s,likes l where l.beer=s.beer and f.bar=s.bar and f.drinker=l.drinker))

It's enough if someone can explain me how to implement where not in in TRC no need to convert the entire query.I am using http://www-rohan.sdsu.edu/~eckberg/relationalcalculusemulator.html
to check my relational calculus and convert it into sql query.

Note:

If you use implications in your query.

It does not support implication.For example implication can be implemented as follows.(p==>q) can be written as (not p or q) form as both are logically equivalent.

Upvotes: 0

Views: 2395

Answers (3)

user1613360
user1613360

Reputation: 1314

I rewrote my query with where exists and where not exists and now its easy to convert it into relational calculus.The answer is

{T.drinker|∃f2Єfrequents (∀f1Єfrequents (∃fЄfrequents(∃sЄserves ∃lЄlikes(s.beer=l.beer^l.drinker=f.drinker^s.bar=f.bar^f1.drinker=f.drinker^f.bar=f1.bar^f2.bar=f1.bar v f2.drinker≠f1.drinker)))}

Anyways thanks for the inputs.

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25852

well what you are describing is WHERE NOT EXISTS(subquery)

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

let me know if thats not what you want.

also why dont you just change the logic of your statement to go from

WHERE drinker NOT IN (drinker) to WHERE IN(drinker=null)

INITIAL QUERY.. formatted so its easier to read

SELECT 
    DISTINCT f2.drinker 
FROM frequents f2 
WHERE f2.drinker NOT IN 
    (
        SELECT 
            f1.drinker 
        FROM frequents f1 
        WHERE (f1.bar,f1.drinker) NOT IN 
        (
            SELECT 
                f.bar,
                f.drinker 
            FROM frequents f,
                serves s,
                likes l 
            WHERE l.beer=s.beer AND f.bar=s.bar AND f.drinker=l.drinker
        )
    )

what you should be able to do is this

SELECT 
    DISTINCT f2.drinker 
FROM frequents f2 
WHERE f2.drinker IN 
    (
        SELECT 
            f1.drinker 
        FROM frequents f1 
        WHERE f1.drinker IS NULL AND (f1.bar,f1.drinker) IN 
        (
            SELECT 
                f.bar,
                f.drinker 
            FROM frequents f,
                serves s,
                likes l 
            WHERE l.beer=s.beer AND f.bar=s.bar AND f.drinker=l.drinker AND f.drinker IS NULL
        )
    )

hopefully this works, i can't really test it. but the idea is instead of saying where this is not in this say where this is in this where id (in subquery) is null.

Upvotes: 0

Hamidreza
Hamidreza

Reputation: 3128

Your query is equal your inner query my friend,because you said f2 is something which is not in f1 and f1 is something which is not in f and all of them got one source frequents and it means f2=f and your query returns this:

select f.bar,f.drinker from frequents f,serves s,likes l 
where l.beer=s.beer and f.bar=s.bar and f.drinker=l.drinker;

but i think this query can give you better results my friend:

select f.bar,f.drinker from frequents f 
left outer join serves s on  f.bar=s.bar 
left outer join likes l on l.beer=s.beer and f.drinker=l.drinker;

Tack a look at SQL Fiddle, it will help you to get it better.

Upvotes: 0

Related Questions