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

Mysql join showing wrong result in sql

I am working on PHP with mysql, Right now i have following two tables,And i want to display minimum value according to collection id,Here is my first table "nft_info"

id              collection_id           name        
1               18              abc
2               18              xyz
3               19              hax
...

And here is my table "nft_sell_info"

id              nft_id          listing_price
1               1               15
2               2               50
3               3               30
...

Now i want to get minimum value of "listing_price" regarding collection_id=’18’, I tried with following query but giving me wrong result (showing 50 instead of 15 )

SELECT MIN(ns.listing_price) AS lowest_price 
FROM `nft_info` `ni` 
JOIN `nft_sell_info` `ns` 
ON `ns`.`nft_id` = `ni`.`id` 
WHERE `ni`.`collection_id` = '1'
Tags:

3

Answers


  1. 0 
    SELECT MIN(ns.listing_price) AS lowest_price 
FROM `nft_info` AS `ni` 
JOIN `nft_sell_info` AS `ns` 
ON `ns`.`nft_id` = `ni`.`id` 
WHERE `ni`.`collection_id` = '1'

    You need to use the AS aliasing throughout your code because the abbreviation isn’t working without AS.

    Login or Signup to reply.
  2. 0

    You are query is working fine if you change the

    ni.collection_id = ‘1’

    to

    ni`.`collection_id` = '18'

    Here is the code I used to test:

    -- Create nft_info table
CREATE TABLE nft_info (
  id INT NOT NULL AUTO_INCREMENT,
  collection_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- Insert data into nft_info table
INSERT INTO nft_info (id, collection_id, name)
VALUES (1, 18, 'abc'),
       (2, 18, 'xyz'),
       (3, 19, 'hax');

-- Create nft_sell_info table
CREATE TABLE nft_sell_info (
  id INT NOT NULL AUTO_INCREMENT,
  nft_id INT NOT NULL,
  listing_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (nft_id) REFERENCES nft_info(id)
);

-- Insert data into nft_sell_info table
INSERT INTO nft_sell_info (id, nft_id, listing_price)
VALUES (1, 1, 15.00),
       (2, 2, 50.00),
       (3, 3, 30.00);

SELECT MIN(ns.listing_price) AS lowest_price 
FROM `nft_info` `ni` 
JOIN `nft_sell_info` `ns` 
ON `ns`.`nft_id` = `ni`.`id` 
WHERE `ni`.`collection_id` = '18'
    Login or Signup to reply.
  3. 0 
    SELECT MIN(listing_price)
from nft_info t1 
left join nft_sell_info t2 on t1.id = t2.nft_id
where t1.collection_id = 18
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search