CODEWITHSUNDEEP
sqloracle-database
BreakHead
BreakHead

Reputation: 10672

Inner join on the same columns

Customer  Table
----------------------
CustomerName
Peter
Sam


Sales Table
-----------------------
ProductName    Customer
Cloth          Peter
Mobile         Peter
Cloth          Sam
Laptop         Sam

Expected result

Customer
Sam

I want result as customer who buyed 'Cloths' but not 'Mobile', i tried 

select c.CustomerName from Customer c inner join Sales s1 on (s1.customer = c.customername and s1.productname = 'Cloth') inner join Sales s2 on (s2.customer = c.customername and s2.productname != 'Mobile');

but it always return both entries

Customer
Peter
Sam
Sam

Upvotes: 0

Views: 114

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is an example of a "set-within-sets" query. I think a good approach is to use aggregation:

select s.Customer
from Sales s
group by s.Customer
having sum(case when s.ProductName = 'Cloth' then 1 else 0 end) > 0 and  -- has cloth
       sum(case when s.ProductName = 'Mobile' then 1 else 0 end) = 0     -- does not have mobile

I prefer putting the logic in the having clause, because it is quite flexible. You can add additional conditions quite easily for other products.

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

You can use the Oracle MINUS operator to make it simple;

SELECT "Customer" FROM SalesTable WHERE "ProductName"='Cloth'
MINUS
SELECT "Customer" FROM SalesTable WHERE "ProductName"='Mobile'

Another slightly more complex option is a LEFT JOIN; 

SELECT DISTINCT s1."Customer"
FROM SalesTable s1
LEFT JOIN SalesTable s2
ON s1."Customer" = s2."Customer"
   AND s2."ProductName" = 'Mobile'
WHERE s1."ProductName" = 'Cloth'
  AND s2."Customer" IS NULL;

An SQLfiddle to test both with.

Upvotes: 1

Vinicius Lima
Vinicius Lima

Reputation: 544

first of all you should review your database schema.
You never do inner join without an id. Try to create your tables using relationships. Like this:

create table customer
(
id_customer       int not null,
ds_customername   varchar(80) null,
primary key (id_customer)
)

create table products
(
id_product  int not null,
ds_product  varchar(100) null,
primary key (id_product)
)

create table sales
(
id_sales    int not null,
id_product  int not null,
id_customer int not null,
foreign key (id_product) references products (id_product),
foreign key (id_customer) references customer (id_customer)
)

select  customer.ds_customername
from    customer
        inner join sales (customer.id_customer = sales.id_customer)
        inner join products (products.id_product = sales.id_product)
where   products.ds_product = 'Cloth'

Ok, if you can't do this, you can do your query (in an old way) this:

    select   Customer.customername
    from     Customer
             inner join on (customer.customername = sales.customer)
    where    sales.productname = 'Cloth'

I hope help you. Hugs, Vin.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

A correlated subquery would be better as you're not interested in getting multiple rows for customers who bought cloths multiple times.

select
  c.CustomerName
from
  Customer c
where
  exists (
    select null
    from   sales
    where  sales.customer = c.customername and
           s1.productname = 'Cloth') and
  not exists (
    select null
    from   sales
    where  sales.customer = c.customername and
           s1.productname = 'Mobile');

Upvotes: 2

Gonzalo.-
Gonzalo.-

Reputation: 12672

try this:

select c.CustomerName 
from Customer c 
where exists(select 1 from sales s1 where s1.customer = c.customername and s1.productname = 'Cloth')
and not exists (select 1 from sales s2 where s2.customer = c.customername and s2.productname = 'Mobile')

Upvotes: 0

Related Questions