CODEWITHSUNDEEP
mysqlmysql-workbench
Dino
Dino

Reputation: 561

List customers that have bought the same pair of products MySQL

I have the following two tables:

customer

id  | customer
----|---------
1   | john
2   | jenkins
3   | jane
4   | janet
5   | jenny

products

customer_id | product | price
------------|-----------|-----
        1   | brush     | 3.5
        1   | deoderant | 1
        1   | soap      | 2.5
        2   | bread     | 3
        2   | brush     | 3
        2   | soap      | 2.5
        3   | brush     | 3
        4   | deoderant | 1
        4   | soap      | 1
        5   | milk      | 1

So I have to find the pair of customers that have bought the same product and I must remove the duplicate pairs and remove the pairs such as (john, john) for john. The MySQL query that I create is:

select A.customer, B.customer, products.customer_id, products.product from customer A, customer B, products where B.id = products.customer_id and A.customer <> B.customer group by B.customer;

I get the following resulted:

customer|customer|customer_id|product   
--------|--------|-----------|--------
john    | jane   | 3         | brush
john    | janet  | 4         | deoderant
john    | jenkins| 2         | bread
john    | jenny  | 5         | milk
jenkins | john   | 1         | brush

I have been battling with this the whole morning and am I am stuck. I know that it is wrong because jane and jenkins bought the same item.

Upvotes: 0

Views: 3059

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is one way, that puts the customers into one column:

select p.*,
       group_concat(distinct c.customer) as customers
from customers c join
     products p
     on c.id = p.customer_id
group by p.id;

If you actually want pairs:

select c.id, c.customer, c2.id, c2.customer,
       p.product
from customers c join
     customers c2
     on c.id <> c2.id join
     products p
     on c.id = p.customer_id join
     products p2
     on c2.id = p2.customer_id and
        p.id = p2.id;

You might want select distinct, if customers could purchase the same product more than one.

Upvotes: 2

Related Questions