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 f开发者_C百科ind 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?

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#
     )

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')