Join help needed

[Zaggs]

Hi Guys,

I have the following MySQL query, which works fine. However, I would only like to show the current high offer from the bids table.

Here is my current query:

SELECT domains.*, domains.bids AS total_bids FROM domains LEFT JOIN bids ON bids.domain_id = domains.id WHERE domains.domain_name LIKE '%domain.com%' AND domains.status='1' GROUP BY domains.id ORDER BY domain_name ASC 

To show the current high offer from a new MySQL query I would do the following:

select bids.offer as high_offer from bids where domain_id='123' order by bid.offer desc limit 1 

But the question is - how can I pull the latest high bid in the left join sql query above?

Thanks.

[guido2004]

SELECT 
    domains.*
  , domains.bids AS total_bids 
  , MAX(bids.offer) AS high_offer
FROM domains 
LEFT JOIN bids 
ON bids.domain_id = domains.id 
WHERE domains.domain_name LIKE '%domain.com%' 
AND domains.status='1' 
GROUP BY domains.id 
ORDER BY domain_name ASC

I was wondering though, why do you select domains.bids two times?

[Zaggs]

Thanks - that worked perfect and you're right, I have selected domains.bids twice. Thanks for pointing that out!