Using SQL(ite) how do I find the lowest unique child for each parent in a one to many relationship during a JOIN?

I have two tables with a many to one relationship which represent lots and bids within an auction system. Each lot can have zero or more bids associated with it. Each bid is associated with exactly one lot.

My table structure (with irrelevant fields removed) looks something like this:

For one type of auction the winning bid is the lowest unique bid for a given lot.

E.g. if there are four bids for a given lot: [1, 1, 2, 4] the lowest unique bid is 2 (not 1).

So far I have been able to construct a query which will find the lowest unique bid for a single specific lot (assuming the lot ID is 123):

SELECT id, value FROM bid
WHERE lot = 123 
AND amount = (
    SELECT value FROM bid 
    WHERE lot = 123
    GROUP BY value HAVING COUNT(*) = 1 
    ORDER BY value
)

This works as I would expect (although I'm not sure it's the most graceful approach).

I would now like to construct a query which will get the lowest unique bids for all lots at once. Essentially I want to perform a JOIN on the two tables where one column is the result of something similar to the above query. I'm at a loss as to how to use the same approach for finding the lowest unique bid in a JOIN though.

Am I on the wrong track with this approach to finding the lowest unique bid? Is there another way I can achieve the same result?

Can anyone help me expand this query into a JOIN?

Is this ev开发者_StackOverflowen possible in SQL or will I have to do it in my application proper?

Thanks in advance.

(I am using SQLite 3.5.9 as found in Android 2.1)

You can use group by with a "having" condition to find the set of bids without duplicate amounts for each lot.

          select lotname, amt
          from lot inner join bid on lot.id = bid.lotid
          group by lotname, amt having count(*) = 1

You can in turn make that query an inline view and select the lowest bid from it for each lot.

select lotname, min(amt)
from
(
  select lotname, amt
          from lot inner join bid on lot.id = bid.lotid
          group by lotname, amt having count(*) = 1
) as X

group by X.lotname

EDIT: Here's how to get the bid id using this approach, using nested inline views:

 select bid.id as WinningBidId, Y.lotname,  bid.amt
from
bid
join
(   
   select x.lotid, lotname, min(amt) as TheMinAmt
from
(
    select lot.id as lotid, lotname,  amt 
    from lot inner  join bid   on lot.id = bid.lotid
    group by lot.id, lotname, amt
    having count(*)=1 
) as X

group by x.lotid, x.lotname
) as Y
on Y.lotid = bid.lotid and Y.TheMinAmt = Bid.amt

I think you need some subqueries to get to your desired data:

SELECT [b].[id] AS [BidID], [l].[id] AS [LotID], 
   [l].[Name] AS [Lot], [b].[value] AS [BidValue]
FROM [bid] [b]
INNER JOIN [lot] [l] ON [b].[lot] = [l].[id]
WHERE [b].[id] = 
   (SELECT TOP 1 [min].[id] 
    FROM [bid] [min] 
    WHERE [min].[lot] = [b].[lot]
        AND NOT EXISTS(SELECT * 
                       FROM [bid] [check] 
                       WHERE [check].[lot] = [min].[lot] 
                           AND [check].[value] = [min].[value] 
                           AND [check].[id] <> [min].[id])
    ORDER BY [min].[value] ASC)

The most inner query (within the exists) checks if there are no other bids on that lot, having the same value. The query in the middle (top 1) determines the minimum bid of all unique bids on that lot. The outer query makes this happen for all lots, that have bids.

SELECT lot.name, ( SELECT MIN(bid.value) FROM bid Where bid.lot = lot.ID) AS MinBid
FROM Lot INNER JOIN
    bid on lot.ID = bid.ID

If I understand you correctly this will give you everylot and their smallest bid