sql query with two tables having reference key

Question

I have two tables:

Discounts(disid primary key)

Cust(custid primary key, disid ref discount(disid))

Now I need a query to get custid having all disid(discount coupons) and the customer may contain the same disid more than once.

A simple join won't do it? If not more info needed – Tony Hopkinson Aug 04 '12 at 14:48 — Tony Hopkinson, Aug 04 '12 at 14:48

score 1 · Answer 1 · answered Aug 04 '12 at 14:47

1

select custid, count(distinct disid) from cust
group by custid
having count(*) = (select count(*) from discounts)

answered Aug 04 '12 at 14:47

hol

8,255
5
33
59

score 1 · Answer 2 · answered Aug 04 '12 at 14:49

1

SELECT COUNT(DISTINCT D.disid) FROM CUST C 
INNER JOIN DISCOUNTS D ON D.disid=C.disid GROUP BY D.disid

answered Aug 04 '12 at 14:49

Samson

2,801
7
37
55

score 1 · Answer 3 · answered Aug 04 '12 at 14:54

try either of this solutions:

SELECT  a.custid, COUNT(a.disid) totalCoupon
FROM    cust a  
            INNER JOIN discounts b 
                ON b.disid = a.disid 
GROUP BY a.custid

or

SELECT  a.custid, COUNT(a.disid) totalCoupon
FROM    cust a  
            INNER JOIN discounts b 
                ON b.disid = a.disid 
GROUP BY a.custid
HAVING   COUNT(a.disid) > 1 -- customers having the same (but more than 1) 
                            -- CouponID will only be shown here

sql query with two tables having reference key

3 Answers3