SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    NJ/NY
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Experts! Help with structuring complex query

    Hey all,
    I have the following query that I'll like to create:

    SELECT *, count(u.userid) AS faves_result,
    count(case when f.bandID IN ($qstr) then 937 end) AS matches,
    (count(u.userid)+$faves_primary-2*count(case when f.bandID IN ($qstr) then 937 end)) AS delta
    FROM m_user u
    LEFT OUTER JOIN m_fan f
    ON u.userid=f.userid
    WHERE u.userid!='$userid' AND delta<20
    GROUP BY u.userid
    ORDER BY matches DESC
    LIMIT 0,5

    I know the portion in red "AND delta<20" is illegal, but I'm not sure how I can structure this query to simulate that clause. Essentially, I only want to pull the rows where delta (as defined in the SELECT section of the query) is less than 20.

    Please let me know if you have any questions. Thanks.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code mysql:
    SELECT u.*
          , count(u.userid) AS faves_result
          , count(case when f.bandID IN ($qstr) then 937 end) AS matches
          , (count(u.userid)+$faves_primary-2*count(case when f.bandID IN ($qstr) then 937 end)) AS delta
      FROM m_user u
    LEFT OUTER
      JOIN m_fan f
        ON u.userid=f.userid
     WHERE u.userid!='$userid'
    GROUP
        BY u.userid
    HAVING delta<20
    ORDER
        BY matches DESC
     LIMIT 0, 5
    * is bad enough, but mixing GROUP BY and * is a really bad idea. i made it slightly less evil by qualifying with a table alias, but you should specify exactly which columns you need.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    NJ/NY
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the query and advice!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    937, heh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ya, i was impressed with that, too.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    wonder where he got that from, eh

    i mean, usually people write SUM(CASE ... THEN 1 ELSE 0 END)

    not COUNT(CASE ... THEN 937 END)

    that's a bit devious

    rudy.ca | @rudydotca
    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
  •