SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left join with last record

    I need to join with last record of "purchased_products" table to get the latest actual balance.


    SELECT * FROM {users} u
    LEFT JOIN {role_expire} re ON u.uid=re.uid
    LEFT JOIN {purchased_products} pp ON re.uid=pp.uid
    WHERE (re.expire <= NOW() OR re.expire is NULL) || (pp.balance < 0 OR pp.balance IS NULL)

    What is the best way to that?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT * 
      FROM {users} u 
    LEFT OUTER
      JOIN {role_expire} re 
        ON re.uid = u.uid 
       AND re.expire <= NOW()
    LEFT OUTER
      JOIN ( SELECT uid
                  , MAX(purchase_date) AS last_date
               FROM {purchased_products}
              WHERE balance < 0
             GROUP 
                 BY uid ) AS xxx
        ON xxx.uid = u.uid
    LEFT OUTER
      JOIN {purchased_products} AS pp 
        ON pp.uid = u.uid
       AND pp.purchase_date = xxx.last_date
       AND pp.balance < 0
     WHERE re.expire <= NOW() 
        OR re.expire is NULL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •