selecting more than one row of matching data

Question

Hi, I need to select the number (cid) of a customer that has rented the same movie from 2 different branches. My tables are as follows:

RENTED
(cid, copyid)
12345  99999
12345  88888

COPY
(copyid, mid, bid)
99999    444  123
88888    444  456

So one customer (12345) has rented the same move (444) from two different branches (123, 456). I am not sure how to compare the values where in two different records, the values mid = mid but bid != bid. I tried to use 'some' and 'all' but this gives me no rows (code below)

select cid
from rented R join copy CP on R.copyid = CP.copyid
where CP.mid = all (select mid from copy where CP.mid = copy.mid) and CP.bid != some (select bid 
from copy where CP.bid = copy.bid);

and my output should be

cid
12345

Answer 1

Hi, you could use the HAVING clause. The following query will list all customers who have ever rented the same movie several times:

SELECT r.cid
  FROM rented r
  JOIN copy p ON r.copyid = p.copyid
 GROUP BY r.cid, p.mid
 HAVING COUNT(DISTINCT c.bid) > 1

Answer 2

Using a single pass on each table:

select distinct(cid) from ( select cid, count(bid) over (partition by r.cid,c.mid) dist_branch from rented r, copy c where r.copyid = c.copyid) where dist_branch > 1;