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

Unable to get lowest value using join – Mysql

I have two tables info and sell_info. info table contains "list of record" and sell_info contains "product which are on sale".

I want to get "lowest" value of record where id = 1. How can i do this?

Here is my table "info":

id name
1 ABC
2 XYZ
3 CDE
… …

Here is my table "sell_info":

id product_id price
1 1 5
2 1 3
3 1 8
4 2 2
… … …

Expected result is (getting lowest price of id="1"):

id name price
1 ABC 3
Tags:

3

Answers


  1. 0

    The following query would give you what you want.

    It uses an inner query to get the minimum price for each product ID from the SELL_INFO table. It then uses the product ID from that inner query to join to the INFO table, to get the name of the product.

    SELECT
  INFO.ID, 
  INFO.NAME,
  MINP.MIN_PRICE
FROM
  INFO as INFO
  INNER JOIN 
  (
  SELECT 
    SI.PRODUCT_ID as PRODUCT_ID, 
    min(SI.PRICE) as MIN_PRICE
  FROM
    SELL_INFO as SI
  GROUP BY
    SI.PRODUCT_ID
  ) as MINP
    ON INFO.ID = MINP.PRODUCT_ID
WHERE
MINP.PRODUCT_ID = 1
    Login or Signup to reply.
  2. 0

    This should work

    SELECT i.id, i.name, MIN(s.price) as price
FROM info as i, sell_info as s
WHERE i.id = s.product_id AND i.id = 1
GROUP BY i.id, i.name
    Login or Signup to reply.
  3. 0

    It’s quite easy.
    Here is a SQLFIDDLE.

    select i.id, i.name, s.price
from info i
JOIN sell_info s ON i.id = s.product_id
WHERE i.id = 1 AND s.price = (
    SELECT MIN(price)
    FROM sell_info
    WHERE product_id = 1
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search