SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join help needed

    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:

    PHP Code:
    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:

    PHP Code:
    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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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?

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks - that worked perfect and you're right, I have selected domains.bids twice. Thanks for pointing that out!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •