I m not sure how to return the highest value from the joined table. There are currently 4 items in the table but the query is only returning one. The MAX(b.bid) is breaking the query.
SELECT i.id,
i.user_id,
i.item_title,
i.item_description,
i.item_condition,
i.active,
i.add_date,
MAX(b.bid)
FROM items i
LEFT JOIN bids b
ON i.id = b.item_id
WHERE i.user_id = ? AND i.active = 1
ORDER BY i.id DESC
If you want the max(bid) per item, you're going to need to group your query by item.
SELECT i.id,
i.user_id,
i.item_title,
i.item_description,
i.item_condition,
i.active,
i.add_date,
MAX(b.bid)
FROM items i
LEFT JOIN bids b
ON i.id = b.item_id
WHERE i.user_id = ? AND i.active = 1
GROUP BY i.id
ORDER BY i.id DESC
This result depends on all the other fields being the same for each bid, which it looks like it should be. This is a valid use of the mysql extension to group by. Otherwise you'd need to specify each field in the select in the group by clause.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments