this simple query question making my life hell!

What is needed: The system should provide the facility to view a list of vehicles currently under auction, together with their reserve prices, the number of bids for each vehicle and the current highest bid for each one.

These are the access tables:



reserve price is a column in the stock table, the owner checks a box AuctionCar to make it visible to the auction and enters the reserve price there.

Moving onto the bids table, here we see auction cars being bid on, there is an auctionID, id(this is the carID), userID and their bid. Simple.

So why on earth am I having so much trouble trying to create a simple report page that list all cars from the bids table, tells me how many bids they have and the highest bid placed for that car?? I can't even seem to understand how to do it as a concept, any help appreciated! thanks

The system should provide the facility to view a list of vehicles currently under auction, together with their reserve prices, the number of bids for each vehicle and the current highest bid for each one.

how do you track if the auction for a particular vehicle has ended or not? also the number of bids for each vehicle? i couldn't see any field to represent these info on your tables.

at the moment the owner concludes the auction whenever he wants, just to keep it simple, so there's no time element yet.

number of bids each vehicle should work like this: basically in the bids table the vehicle no 47 occurs twice, so that would mean it has two bids, simple as that, and the rest have one bid each.

My problem is that I don't know how to sum up these occurences for each distinct id number...

Highest bid would be, for example in the case of vehicle 47, 6400 (out of 5000 and 6400 it is the largest bid placed), so this is pretty simple too but dont know how to query it...thats it

First, it should be noted that things that sound simple to explain can sometimes be difficult to write SQL for. That said, I think something like this might work (not tested though, obviously):

select s.ID, s.makeID, s.modelID, max(b.highBid) as highestBid, count(b.id) as bidCount
from
stock s,
bids b
where s.id = b.id
group by s.ID, s.makeID, s.modelID

I just tried it, I think it's pretty close, but for some reason no results are displayed (i got cfoutput query setup don't worry!).

if possible could you briefly explain this query, in particular the groupings? I think it's very close!

To test whether the query is working or not just dump it:

<cfdump var="#myQueryName#">

Info on SQL's GROUP BY operator: http://www.w3schools.com/sql/sql_groupby.asp

thanks man, ur amazing, i got it working, ur sql just needed a few tweaks:

<cfquery name="getids" datasource="hj">
SELECT s.id, s.makeID, s.modelID, s.AuctionReserve, s.AuctionCar, MAX(b.highBid) as highestBid, COUNT(b.id) as bidCount
FROM
stock s LEFT JOIN bids b
ON s.id = b.id
WHERE s.AuctionCar = 1
GROUP BY s.id, s.makeID, s.modelID, s.AuctionReserve, s.AuctionCar
</cfquery>

the s.AuctionCar is a checkbox, if it's checked then the car is an auction car (also just want auction cars listed...)

and left join seemed to do the trick...I'm amazed that all that information can be taken from just one query!! I honestly thought it would be impossible

Not sure why you'd need to use a LEFT JOIN because I can't imagine a situation where you would have bids with no corresponding ID in the stock table. But whatever works.