Mysql Self join data

Question

I have one table

distributor_id | product_id | price
1              | 1          | 10.00
1              | 2          | 11.00
1              | 3          | 12.00
1              | 1          | 14.00
4              | 1          | 9.00
4              | 2          | 32.00
4              | 5          | 17.00

I want to compare prices of products of distributors i.e i want to get output like:

distributor1|distributor2|product_id|distributor1_price|distributor2_price
1           | 4          | 1        |12.00             |9.00
1           | 4          | 2        |11.00             |32.00
1           | 4          | 3        |12.00             |null

distributor1_price,distributor2_price will be the average price by the product_id. product_id should be all products of the distributor1. If distributor2 does not have that product there should be null.

I have tried it with self-join without any success. Thanks.

Answer 1

Haven't tested, but I think this should work:

SELECT a.distributor_id
    ,b.distributor_id
    ,a.product_id
    ,AVG(a.price)
    ,AVG(b.price)
FROM mytable AS a
LEFT JOIN mytable AS b ON a.product_id = b.product_id
GROUP BY a.product_id