Create and use temp table in nested queries

Question

I have this query where the two operands of the MINUS operators are computed twice. How do rewrite the query so that they are only created once?

(The objective is to check that the two sets are equal)

SELECT DISTINCT t1.id
FROM t1
WHERE NOT EXISTS (SELECT t2.did FROM t1 AS t2
              WHERE t2.id = t1.id
              MINUS
              SELECT t3.did FROM t3
              WHERE t3.price > 500000)
AND NOT EXISTS (SELECT t3.did FROM t3
            WHERE t3.price > 500000
            MINUS
            SELECT t2.did FROM t1 AS t2
            WHERE t2.id = t1.id);

To clarify things, the following two [sub]queries are repeated twice in the query:

SELECT t2.did FROM t1 AS t2
WHERE t2.id = t1.id

and

SELECT t3.did FROM t3
WHERE t3.price > 500000

Can you post some sample data and desired results? Not sure we completely understand your requirements. — sgeddes, Mar 16 '13 at 03:32
I've updated the post with the two queries that are computed twice. I was asking how to create a temp table/relation in the query so that the results can be reused. — One Two Three, Mar 16 '13 at 03:41

Egor Skriptunoff · Accepted Answer · 2013-03-16T07:16:52.747

2

with
   ALL_DID as (
      select did from t3
      where price > 500000
   ),
   PAIRS as (
      select
         id, t3.did
      from t1
         left join ALL_DID t3
         on t1.did = t3.did
   )
select id from PAIRS
   group by id
   having count(did) = (
      select count(0) from ALL_DID
   )
minus
select id from PAIRS
   where did is null

fiddle

edited Mar 16 '13 at 07:16

answered Mar 16 '13 at 07:07

Egor Skriptunoff

23,359
2
34
64

sgeddes · Answer 2 · 2013-03-16T04:25:22.667

0

Look into using Common Table Expressions if you just want to be able to reuse the same query multiple times. Or you can look into creating views. Those ways you can "reuse" the same query quickly.

However, I still don't completely understand what your desired results would be. Are you wanting to know if the data in t1 is the same as the data in t3, based on the did/eid fields? If that is the case, it seems you could use a LEFT JOIN to figure that out much more easily. Something like this:

SELECT DISTINCT t1.id
FROM t1 
  LEFT JOIN t3 ON t1.did = t3.did AND t3.price > 500000
WHERE t3.id IS NULL

This will tell you if there are any records in t1 that aren't in t3 (with the same did and price > 500000).

EDIT -- To find the id of all persons who sell all products that have prices greater than 500000, then this should work:

SELECT t1.id, COUNT(DISTINCT t1.did) productcnt
FROM t1
  JOIN t3 ON t1.did = t3.did
WHERE t3.Price > 500000
GROUP BY t1.id
HAVING COUNT(DISTINCT t1.did) = (
  SELECT COUNT(DISTINCT did) cnt
  FROM t3
  WHERE Price > 500000 )

SQL Fiddle Demo

edited Mar 16 '13 at 04:25

answered Mar 16 '13 at 03:50

sgeddes

62,311
6
61
83

I made some typo in the question while trying to simplify the query. Here's what I want to do originally. The two tables are `t3 : (price, did)` `t1: (did, id)` Find all `t3.did` whose `price > 500000` (called this set s1). Then find all the `t1.id` such that the set `select t1.did` is equal to `s1` – One Two Three Mar 16 '13 at 04:05
@OneTwoThree -- you might be better off posting sample data and desired results (perhaps a SQL Fiddle). It sounds like you just want an INNER JOIN: SELECT t1.id FROM T1 JOIN T3 ON T1.did = T3.did AND T3.Price > 500000; – sgeddes Mar 16 '13 at 04:10
Maybe if I translate this to plain English, it'd make more sense. If `t1.id` is the id of a person selling a product that is identified by `t1.did`, and if `t3` is the product table (where `t3.did` is the product's id and `price` is its price), then the query would be `Find the id of all persons who sell all products that have prices greater than 500000` – One Two Three Mar 16 '13 at 04:15
@OneTwoThree -- Is this what you're looking for? http://sqlfiddle.com/#!4/8bcd2/3 – sgeddes Mar 16 '13 at 04:24
I honestly don't really understand the query, especially the part where you use COUNT. Why is that necessary? (Just to be sure, does my description of the query make sense?) – One Two Three Mar 16 '13 at 04:40
@sgeddes - Your query gives incorrect result with additional `insert into t1 values (1, 0);` – Egor Skriptunoff Mar 16 '13 at 07:09

georgecj11 · Answer 3 · 2013-03-16T04:25:57.217

-1

Isnt it normal inner join query?

SELECT t2.* 
from t1
INNER JOIN t3 as t3 on t1.did = t3.tid
INNER JOIN t1 as t2 on t2.did = t1.id
WHERE t3.price > 50000

edited Mar 16 '13 at 04:25

answered Mar 16 '13 at 04:14

georgecj11

1,600
15
22

No, because this means you'd get the 'id' whose corresponding 'did' had 'price' greater than 500000. That is different from what the query in the question does. – One Two Three Mar 16 '13 at 04:17
oh sorry, typo from me end. Last join clause shld be t1.id = t2.did – georgecj11 Mar 16 '13 at 04:25

Create and use temp table in nested queries

3 Answers3

Linked