Complex select statement

Hi,

I’m building an auction style site. The “auctions” are grouped into “sales” and each sale has a closing date/time. Currently to display a list of “active” (running) sales I use this simple SELECT statement:

"SELECT saleId, salename, saleclosingdate FROM tblsales WHERE salenabled = 'Y' AND saleclosingdate > Now() ORDER BY saleclosingdate ASC"

(where salenabled is a simple Y or N depending on whether the sale is enabled (over-riding the closing date/time) and saleclosingdate is the DATETIME closing date/time)

Within each “Sale” are “lots” (database table: tbllots), each lot has the following (relevant) fields:

tbllots.lotId (unique auto increment INT)
tbllots.lotsaleId (INT - which sale (saleId) this lot is related to)
tbllots.lotnum (VARCHAR 100 - a short text description of the lot)

So far, so good. However, my client would like to add an “anti-sniping” feature at LOT level. They want to enable each LOT to extend (it’s closing date/time) by a set time (minutes) should anybody place a bid in the last (eg) 5 minutes to allow other bidders to have time to place another bid, only when there had been no (bidding) activity on the LOT during the last 5 minutes would the LOT close. All other LOTs would close at the closing date/time set in the sale.

So what I need is for my SELECT statement to return “Sales” which either haven’t reached their closing date/time (saleclosingdate) OR sales that have passed their closing date but have any LOTs with anti-sniping enabled and if they do have anti-sniping enabled check whether any bids have been received in the last X minutes.

I’ve added two new fields to tbllots:
tbllots.lotantisnipe (VARCHAR 1 (Y if enabled, N if not enabled)
tbllots.lotantisnipemin (INT) value in minutes of how long to leave lot open after last bidding activity.

Bids made by clients are stored in the tblbids table, the relevant fields are:
tblbids.bidId (unique auto increment INT)
tblbids.bidsaleId (INT - which sale (saleId) this bid is related to)
tblbids.bidlotId (INT - which LOT (lotId) this bid is related to)
tblbids.biddatetime (DATETIME of bid)

I really hope someone can help me with this as I can’t see anyway of makign the SELECT statement with so many conditions.

Many thanks for taking the time to read this.

Try (didn’t test it)


SELECT 
    tblsales.saleId
  , tblsales salename
  , tblsales.saleclosingdate 
FROM tblsales 
INNER JOIN tbllots
ON tblsales.saleId = tbllots.lotsaleId
LEFT OUTER JOIN tblbids
ON tbllots.lotId = tblbids.bidlotId
WHERE salenabled = 'Y' 
AND (   saleclosingdate > Now() 
     OR (    tbllots.lotantisnipe = 'Y'
         AND tblbids.bidlotId IS NOT NULL
         AND tblbids.bidsdatetime + INTERVAL tbllots.lotantisnipemin MINUTE > NOW()
        )
    )
ORDER BY tblsales.saleclosingdate ASC

Hi Guido,

Thank you very much for the speedy response. It almost works but I think it needs a GROUP BY statement - at the moment I’m expecting the recordset to return 3 records (sales) but I get a repeated entry for eahc LOT in each sale (i.e. Sale ID 2 is repeated 25 times which is how many LOTs are in that Sale). Would I just need to GROUP BY saleId?

Thanks again.

Ah yes, but since you’re not using any group functions (like MAX(), SUM() etc) you can use SELECT DISTINCT

Thanks so much for your help. It works wonderfully!

You’re a genius!