Joining table with min(amount) does not work

Question

I have 3 tables, but data is only fetch from 2 tables.

I'm trying to get the lowest bids for selected items and display user name with the lowest bid.

Currently query works until when we display user name, it shows wrong user name, which does not match the bid.

Below is working example of structure and query.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE `bid` (
  `id` int(11) NOT NULL,
  `amount` float NOT NULL,
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


INSERT INTO `bid` (`id`, `amount`, `user_id`, `item_id`) VALUES
(1, 9, 1, 1),
(2, 5, 2, 1),
(3, 4, 3, 1),
(4, 3, 4, 1),
(5, 4, 2, 2),
(6, 22, 5, 1);

-- --------------------------------------------------------

CREATE TABLE `item` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `item` (`id`, `name`) VALUES
(1, 'chair'),
(2, 'sofa'),
(3, 'table'),
(4, 'box');

-- --------------------------------------------------------

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


INSERT INTO `user` (`id`, `name`) VALUES
(1, 'James'),
(2, 'Don'),
(3, 'Hipes'),
(4, 'Sam'),
(5, 'Zakam');


ALTER TABLE `bid`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `item`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `user`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `bid`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;

ALTER TABLE `item`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Query 1:

SELECT b.id, b.item_id, MIN(b.amount) as amount, b.user_id, p.name
FROM  bid b
LEFT JOIN user p ON p.id = b.user_id
WHERE b.item_id in (1, 2)
GROUP BY b.item_id
ORDER BY b.amount, b.item_id

Results:

| id | item_id | amount | user_id |  name |
|----|---------|--------|---------|-------|
|  5 |       2 |      4 |       2 |   Don |
|  1 |       1 |      3 |       1 | James |

Explanation of query:

• Get the selected items (1, 2).
• get the lowest bid for thous items - MIN(b.amount)
• display user names, who has given the bid - LEFT JOIN user p on p.id = b.user_id (this is not working or I'm doing something wrong)

[Note] I can't use sub-query, I'm doing this in doctrine2 (php code) which limits mysql sub-query

Answer 1

No, you are not necessarily fetching the user_id who has given the bid. You group by item_id, so you get one result row per item. So you are aggregating and for every column you say what value you want to see for that item. E.g.:

• MIN(b.amount) - the minimum amount of the item's records
• MAX(b.amount) - the maximum amount of the item's records
• AVG(b.amount) - the avarage amount of the item's records
• b.amount - one of the amounts of the item's records arbitrarily chosen (as there are many amounts and you don't specify which you want to see, the DBMS simply choses one of them)

This said, b.user_id isn't necessarily the user who made the lowest bid, but just one random user of the users who made a bid.

Instead find the minimum bids and join again with your bid table to access the realted records:

select bid.id, bid.item_id, bid.amount, user.id as user_id, user.name
from bid
join 
(
  select item_id, min(amount) as amount
  from bid
  group by item_id
) as min_bid on min_bid.item_id = bid.item_id and min_bid.amount = bid.amount
join user on user.id = bid.user_id
order by bid.amount, bid.item_id;

Answer 2

You can solve this using a subquery. I am not 100% sure if this is the most efficient way, but at least it works.

SELECT b1.id, b1.item_id, b1.amount, b1.user_id, p.name
FROM  bid b1
LEFT JOIN user p ON p.id = b1.user_id
WHERE b1.id = (
   SELECT b2.id
   FROM bid b2
   WHERE b2.item_id IN (1, 2)
   ORDER BY b2.amount LIMIT 1
)

This first selects for the lowest bid with for item 1 or 2 and then uses the id of that bid to find the information you need.

Edit

You are saying that Doctrine does not support subqueries. I have not used Doctrine a lot, but something like this should work:

$subQueryBuilder = $entityManager->createQueryBuilder();
$subQuery = $subQueryBuilder
    ->select('b2.id')
    ->from('bid', 'b2')
    ->where('b2.item_id IN (:items)')
    ->orderBy('b2.amount')
    ->setMaxResults(1)
    ->getDql();

$queryBuilder = $entityManager->createQueryBuilder();
$query = $queryBuilder
    ->select('b1.id', 'b1.item_id', 'b1.amount', 'b1.user_id', 'p.name')
    ->from('bid', 'b1')
    ->leftJoin('user', 'p', 'with', 'p.id = b1.user_id')
    ->where('b1.id = (' . $subQuery . ')')
    ->setParameter('items', [1, 2])
    ->getQuery()->getSingleResult();