CODEWITHSUNDEEP
sqlplsqloracle9i
sandy
sandy

Reputation: 191

Pl/SQL - oracle 9i

We have a table customer and table car.

customer table is defined as: cust#, transaction# car table is defined as: transaction#, car model#

car model# can be either nissan, toyota or honda.

what we need to find out is how many distinct customer have bought a honda but not a nissan. there can be multiple records for car model# as a customer can buy 2-3 hondas or nissans. Transaction # is primary key in car table.

What would be the most cost effective way of doing this?

Upvotes: 1

Views: 104

Answers (2)

Piyush Mattoo
Piyush Mattoo

Reputation: 16153

SELECT COUNT(DISTINCT cust.CUST#) AS COUNT FROM CUSTOMER cust INNER JOIN CAR car ON  
cust.TRANS#=car.TRANS# WHERE CAR_MODEL#='HONDA'
AND NOT EXISTS 
(SELECT COUNT(1) FROM CUSTOMER inner_cust INNER JOIN CAR inner_car ON 
inner_cust.TRANS#=inner_car.TRANS# 
AND inner_cust.CUST#=inner_car.CUST# WHERE inner_car.CAR_MODEL#='NISSAN')

Upvotes: 1

Chandu
Chandu

Reputation: 82943

Try this:

SELECT COUNT(DISTINCT cust#)
  FROM customer a, car b
 WHERE a.transaction# = b.transaction#
   AND b.model# = 'HONDA'
     AND NOT EXISTS 
     (
        SELECT 1
          FROM customer c, car d
         WHERE c.transaction# = d.transaction#
             AND d.model# = 'NISSAN'
             AND c.cust# = a.cust#
     )

Upvotes: 1

Related Questions