Question 21538 in Mysql Question posted in
The official MySQL documentation can be found here.

How to fetch price for all suppliers in joined table – Mysql

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?

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search