Problem 1: Same data [product_id] primary key in both tables? Then why bother with two tables? You declared a 1:1 relationship between products and bids.
In practice, the bid table should be using the product_id of product_details as a
foreign key . Now you can have multiple bids referred to by a single product.
Once that is done, there are several methods to get your desired result.
Not knowing your table layout, here's the gist of it:
- Group by the product_id in the bid_details table and use MAX(bid-field)
- JOIN the result of this to the product_details table.
I deliberately didn't specify which type of join because I don't know how you wish to handle products that have no current bids (perhaps, handle with ISNULL(bid, 0) ?)
UPDATED: Per your reply
Something like:
SELECT A.item_name, MAX(B.bid) as Bid
FROM product_details as A INNER JOIN bid_details as B
ON A.product_id = B.product_id
GROUP BY A.item_name, B.product_id
But remember, the above relies upon you have tables set up as I alluded to in the first part of this answer.
Submit an article or tip