How to fetch price for all suppliers in joined table

I have the following query

SELECT
    price
FROM prices
left join suppliers s on prices.id_supplier = s.id_supplier
AND prices.id_product = 57;

Table structures are as follows:

Suppliers:

Prices

So based on the data above, I wish to create a query which fetches all prices for all supplier for a given product, even for Supplier 3 (which we do not have a price for in which case it should return 0)

Outpout I require is as follows:

Is this possible?

>Solution :

You have the tables joined in the wrong order.

You want all rows from supplier, so that’s the LEFT table.

SELECT
  s.*,
  COALESCE(p.price, 0)
FROM
  suppliers   s
LEFT JOIN
  prices      p
    ON  p.id_supplier = s.id_supplier
    AND p.id_product  = 57