Need help understanding JOINs

[]|V|[agnus]

I understand the concept, but can't seem to implement it properly.

Table structures:

- AUCTIONS: id | other fields...
- AUCTIONS_BIDS: id | auction | amount | other fields...

When viewing the AUCTIONS, users should be able to filter out records based on whether or not the current high bid for an auction is less than or equal to a value they supply.

I want to select all information from the AUCTIONS table with this query, it doesn't need to return any data from the AUCTIONS_BIDS table. But it does need to be able to reference the AUCTIONS_BIDS table and eliminate auction records in the fashion I described.

As far as I understand JOINs and similar concepts, I am under the impression that this can be achieved with a single query. I'd greatly appreciate some insight into this issue.

[Brandoe85]

I'll take a stab at it, try something like this:

select AUCTIONS.fields from AUCTIONS inner join AUCTIONS_BIDS on AUCTIONS.id = AUCTIONS_BIDS.id where AUCTIONS_BIDS.amount <= the value supplied


I wasn't sure if the amount field was the one being compared...but you get the idea

[]|V|[agnus]

Yes, the amount field is the one being compared. I actually came across the W3Schools SQL tutorials while awaiting a response to this thread, saw the INNER JOIN examples, and thought that might be my solution. Thanks. :)

[]|V|[agnus]

I have been closer than I realized! The JOIN ideas make more sense to me now, but I realize I had already gotten very close. What I have happening now is that it is filtering out the auctions appropriately, but it is returning a copy of the auction record for each auction bid record it finds, a place I'd already gotten to. The missing link?

DISTINCT

Thanks! :)

[Brandoe85]

The powers of one little word.... :D gald you got it going!

[]|V|[agnus]

Bah... jumped the gun. I am closer, but not quite there.

One problem I still have is that auctions with no bids (and therefore no bid records) are not getting returned, even though they effectively have a high bid of zero. That's just a programming issue for me, but...

The other problem is that my query is checking ALL AUCTIONS_BIDS records, and returning a 'match' for the AUCTIONS record if ANY of the bid records meet the criteria.

Basically I need to join on AUCTIONS.id = AUCTIONS_BIDS.auction and [the highest value for the AMOUNT column in any bids records is <= the supplied amount]

[Kiwi]

SELECT DISTINCT A.id
FROM AUCTIONS AS A
LEFT JOIN (AUCTION_BIDS AS B ON A.id = B.auction)
HAVING MAX(B.bid) < <value>

This will return a null value for B.bid if there is no corresponding record in AUCTIOM_BIDS.

[]|V|[agnus]

This query is returning no records:

SELECT DISTINCT a.* FROM auctions AS a
LEFT JOIN auctions_bids AS ab ON a.id = ab.auction
HAVING MAX(ab.amount) <= 70000
AND a.end > {ts '2005-05-12 09:54:09'} ORDER BY a.end DESC

I know that there are several auctions in the database currently with a max bid of less than $70,000. Before I was experimenting with use of INNER JOIN, but that seems to yield the same lack of results.

And Kiwi: when I wrap everything after LEFT JOIN and before HAVING in () like you did, I get a SQL syntax error.