CODEWITHSUNDEEP
sqlselectjointuplesdatabase-schema
mgus
mgus

Reputation: 808

Multiple correlated SQL queries

I have a relational schema with three relations:
Relation Product with attributes (pid, pname, color, weight, city)
Relation Project with attributes (jid, jname, city)
Relation Spj with attributes (sid, pid, jid, quantity)

Each table has some tuples. This is not a realistic problem but an exercise on SQL queries. The query its result I need to find (which consists of 3 correlated queries) is the following:

SELECT p.pname
FROM product p
WHERE NOT EXISTS
      (SELECT *
       FROM project j
       WHERE j.city = 'athens'
       AND NOT EXISTS
           (SELECT *
            FROM spj
            WHERE spj.pid = p.pid
            AND spj.jid = j.jid));

I started calculating the lower query thinking that this is a join between the 3 tables but even from the beginning (I started with joining Spj and Project tables) I ended up having a large table of 14 tuples. So, I think that I might be in the wrong direction.

I'm confused with the two tuple variables (product p, project j) and how to deal with those in general. Can somebody explain how to proceed this query step by step? Is it a "triple" join or not?

Upvotes: 1

Views: 178

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146557

Show me the products that are used on every Project in Athens

is equivalent to

Show me the products where there is no project in Athens that does not use that product.

This last sentence is exactly what the query above says.

SELECT p.pname                      -- Show me the products 
FROM product p
WHERE NOT EXISTS                    -- where there is **no** project 
  (SELECT *
   FROM project j
   WHERE j.city = 'athens'          -- in Athens
      AND NOT EXISTS                -- that does not use
          (SELECT *  FROM spj
           WHERE spj.pid = p.pid    -- that product
             AND spj.jid = j.jid))

Upvotes: 1

Related Questions