Mysql query for comparing price one to each other based on type

example table

name   | source_data | price_a |
---------------------------------
name A | site_a      | 40.00   |
name A | site_b      | 50.00   |
name B | site_a      | 30.00   |
name B | site_b      | 20.00   |

My query in laravel

$data = Product::select('product.device', DB::raw('a.price as price_a'), DB::raw('b.price as price_b'), 'product.date_created')
                ->leftJoin('product as a', function($leftJoin)
                {
                    $leftJoin->on('a.device', '=', 'product.device');
                    $leftJoin->on(DB::raw('a.source_data'), DB::raw('='),DB::raw("'site_a'"));


                })
                ->leftJoin('product as b', function($leftJoin)
                {
                    $leftJoin->on('b.device', '=', 'product.device');
                    $leftJoin->on(DB::raw('b.source_data'), DB::raw('='),DB::raw("'site_b'"));


                })->get();

The Results that i want is :

name   |site_a_price | site_b_price |
---------------------------------
name A | 40.00       |  50.00       |
name B | 30.00       |  20.00       |

I was tried using left join but return the same price_a and price_b for all record.

Answer

You can try using case when expression

    select name,max(case when source_data='site_a' then price end ) as site_a_price,
    max(case when source_data='site_b' then price end ) as site_b_price
    from tablename
    group by name