CODEWITHSUNDEEP
mysqlsqlite
Hani Goc
Hani Goc

Reputation: 2441

For every customer select all the other customers that bought the same item

Database description

I have a simple database composed of three tables: customer, product and custumer_product.

- customer (table)
   id integer primary key not null
   name TEXT

- custumer_product (table)
   id_product integer
   id_customer integer
   primary key(id_product, id_customer)
   FOREIGN KEY(Id_product) REFERENCES product(id)
   FOREIGN KEY (ID_customer) REFERENCES customer(ID)
 
- product (table)
   id integer primary key not null
   name TEXT

The three tables have been initialized in sqlfiddle by using SQLITE. The following SQL queries are used to construct the database

create table if not exists customer (id integer primary key not null, name TEXT);
create table if not exists product  (id integer primary key not null, name TEXT);
create table if not exists customer_product (id_product integer, id_customer 
integer, primary key(id_product, id_customer),  FOREIGN KEY(Id_product) REFERENCES product(id), FOREIGN KEY (ID_customer) REFERENCES customer(ID));

insert into customer(id,name)  values(1,"john");
insert into customer(id,name)  values(2,"Paul");
insert into customer(id,name)  values(3,"Jenny");
insert into customer(id,name)  values(4,"Fred");
insert into customer(id,name)  values(5,"Lea");

insert into product(id,name)  values(1,"Mouse");
insert into product(id,name)  values(2,"screen");
insert into product(id,name)  values(3,"pc");
insert into product(id,name)  values(4,"CD");
insert into product(id,name)  values(5,"Game");

insert into customer_product values(1,1);
insert into customer_product values(1,2);
insert into customer_product values(1,3);

insert into customer_product values(2,1);
insert into customer_product values(2,2);
insert into customer_product values(2,3);

insert into customer_product values(3,4);
insert into customer_product values(4,5);
insert into customer_product values(5,5);

Problem

For every customer I want to select all the other customers that bought at least one similar product.

output

"John" "Paul"
"Jenny"
"Fred" "Lea"

Upvotes: 1

Views: 714

Answers (3)

kenphor
kenphor

Reputation: 32

first find the list of product bought by at least 2 customers, second find the name of the custumers using the join table and third select the customer's name once. here is the query: 

select  distinct c.name from(select c.name, p.name,cp.id_customer, cp.id_product from customer_product cp join customer c on c.id=cp.id_customer join product p on p.id=cp.id_customer where cp.id_product in(select id_product, total from(select id_product,count(*) as total from customer_product group by id_product)p where total>=2)p1)p2)

Upvotes: 0

Ynhockey
Ynhockey

Reputation: 3932

While I'm not entirely sure I understand the conditions, there are three basic steps to this problem, which you can combine into one query (or not).

  1. Get the products that the customer bought
  2. Get the IDs of the customers that bought the same products
  3. Get the customer details based on those IDs

So for 1, you do a simple select:

SELECT id_product FROM customer_product WHERE id_customer = 1

For 2, you can use the IN statement:

SELECT * FROM customer_product WHERE id_product IN
    (SELECT id_product FROM customer_product WHERE id_customer = 1);

For 3 use a combination of JOIN and GROUP BY to get the relevant details from the customer table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

This is basically a self-join and possibly an aggregation. For instance, the following gets all customers that have purchased a similar product as another, ordered by the number of similar products:

select cp.id_customer, cp2.id_customer, count(*)
from customer_product cp join
     customer_product cp2
     on cp.id_product = cp2.id_product
group by cp.id_customer, cp2.id_customer
order by cp.id_customer, count(*) desc;

You can bring in additional information such as customer names by doing additional joins.

Upvotes: 2

Related Questions