Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- customer(cid,city,name,state)
- orders(cid,oid,date)
- product(pid,price,productname)
- lineitem(lid,pid,oid,totalquantity,totalprice)
- Oid Pid
- 2400 1
- 2400 2
- 2401 3
- 2401 1
- 2402 1
- 2403 1
- 2403 3
- select t.oid,l.pid
- from lineitem l
- join (select o.oid,c1.cid
- from orders o
- join (select c.cid
- from customer c
- where c.city='X') c1
- where o.cid=c1.cid) t on l.oid=t.oid
- select pid, count(*)
- from (select t.oid, l.pid
- from lineitem l
- join (select o.oid, c1.cid
- from orders o
- join (select c.cid from customer c where c.city = 'X') c1
- where o.cid = c1.cid) t
- on l.oid = t.oid) x
- group by pid
- having count(*) = (select count(*)
- from (select distinct oid
- from lineitem l
- join (select o.oid, c1.cid
- from orders o
- join (select c.cid
- from customer c
- where c.city = 'X') c1
- where o.cid = c1.cid) t
- on l.oid = t.oid) y) z
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement