How to fetch price for all suppliers in joined table-CodePudding

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:

id_supplier	name
1	Supplier 1
2	Supplier 2
3	Supplier 3

Prices

id_pk	id_product	date	price	id_supplier
1	57	2022-12-29	4.99	1
2	57	2022-12-29	6.99	2

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:

id_supplier	price
1	4.99
2	6.99
3	0

Is this possible?

CodePudding user response：

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

CodePudding user response：

SELECT 
      CASE WHEN price = 57 THEN 57 
      WHEN s.id_supplier IS NULL THEN 0 
      END AS price 
FROM prices
LEFT JOIN suppliers s on prices.id_supplier = s.id_supplier

If I am understanding your problem correctly then this should work. Using case statement will get you price as 57 when price is 57 and when id_supplier IS NULL it will assign 0 to that row. Let me know if this helps.

Edit:

SELECT 
      s.id_supplier,
      CASE WHEN price = 57 THEN 57 
      WHEN price IS NULL THEN 0 
      END AS price
FROM prices
RIGHT JOIN suppliers s on prices.id_supplier = s.id_supplier

This query will select all the suppliers where price is either 57 or null.