SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Need help with complex SELECT

    Hi,

    I need to generate a rather very complex MySQL list and I am having problems getting the right results.

    Description:
    we have 2 Tables:
    community_members
    traffic_xchange

    community_members has a list of Web sites that link back to us, which table has these fields:
    id: INT this is the primary Key
    url: VARCHAR
    display: enum (yes no)
    plus many other fields. so this table gives us the info as to which Web site sent us the click


    traffic_xchange table has these fields:
    url_id which is the foreign key for linking this to community_members table
    clicks_rec: which indicates how many clicks they we have received from the community member
    clicks_del: which indicates how many clicks we have delivered to them in exchange


    So we need to generate a list which shows to us 5 of the Community Members, say selected in random, whose total number of clicks_delivered < clicks_received for a given url of theirs.

    I tried this:

    Code MySQL:
    SELECT url, LEFT(title, 25) AS mini_title, LEFT(description, 45) AS mini_desc, traffic_xchange.id, community_members.id AS url_id, admin_confed
    FROM community_members, traffic_xchange
    WHERE community_members.id = url_id
    AND clicks_del < clicks_rec AND title IS NOT NULL AND display = 'yes'
    GROUP BY url
    ORDER BY RAND() LIMIT 5;


    And it seemed to work correct at 1st, but now I see that it is in fact putting a member on the list even though the sum of clicks_delivered to them is LARGER than the clicks_recived from them which should not be the case.

    So what to do to generate this list so that for a given Web site (url_id) they will be displayed on the list ONLY if the sum of clicks delivered to them is < sum of clicks received from them?

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your problem is the GROUP BY clause

    why did you put that in your query?

    remove it and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    We have the GROUP BY clause so that a Web site (community member) would appear only once in the list.

    But I still did your suggestion, just to see if the problem I had written about was addressed, and it was not.

    Regards,

    Quote Originally Posted by r937 View Post
    your problem is the GROUP BY clause

    why did you put that in your query?

    remove it and see what happens

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    We have the GROUP BY clause so that a Web site (community member) would appear only once in the list.
    then you have a different problem, don't you -- duplicate data in your table

    "community_members has a list of Web sites that link back to us"

    why would you allow the same web site to be listed more than once?

    my advice is to fix that first, not mess around trying to use queries to cover up this flaw
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I looked into your comment just to make sure we had not made the error of listing a community member (web site) more than once in that Table.
    And it is not. That is each community member (Web site) is listed only once in the community_members Table.

    However of course their Traffic exchange reports are listed many times in the traffic_xchange Table, which opens an entry for each day for the Traffic that they send us and we send them in exchange.



    Quote Originally Posted by r937 View Post
    then you have a different problem, don't you -- duplicate data in your table

    "community_members has a list of Web sites that link back to us"

    why would you allow the same web site to be listed more than once?

    my advice is to fix that first, not mess around trying to use queries to cover up this flaw

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    ... an entry for each day
    you should have mentioned this earlier
    Code:
    SELECT community_members.url
         , LEFT(community_members.title, 25) AS mini_title
         , LEFT(community_members.description, 45) AS mini_desc
         , traffic_xchange.id
         , community_members.id AS url_id
         , community_members.admin_confed
      FROM community_members
    INNER
      JOIN ( SELECT DISTINCT
                    url_id
               FROM traffic_xchange 
              WHERE clicks_del < clicks_rec ) AS x        
        ON x.url_id = community_members.id
     WHERE community_members.title IS NOT NULL 
       AND community_members.display = 'yes'
    ORDER 
        BY RAND() LIMIT 5;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT community_members.url
         , LEFT(community_members.title, 25) AS mini_title
         , LEFT(community_members.description, 45) AS mini_desc
         , x.sum_del
         , x.sum_rec
         , x.latest_id
         , community_members.id AS url_id
         , community_members.admin_confed
      FROM community_members
    INNER
      JOIN ( SELECT url_id
                  , SUM(clicks_del) AS sum_del
                  , SUM(clicks_rec) AS sum_rec
                  , MAX(id) AS latest_id
               FROM traffic_xchange 
             GROUP
                 BY url_id
             HAVING sum_del < sum_rec ) AS x        
        ON x.url_id = community_members.id
     WHERE community_members.title IS NOT NULL 
       AND community_members.display = 'yes'
    ORDER 
        BY RAND() LIMIT 5;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My hat off to you
    Your code seems to be working just fine.
    And it has also shown me how much extra free Traffic we have been delivering so many community members!

    Well ThanX again.
    I tell you we should have you be in charge of our MySQL stuff
    OTN, I look and look at your code and cannot figure what the hek it is doing

    Quote Originally Posted by r937 View Post
    Code:
    SELECT community_members.url
         , LEFT(community_members.title, 25) AS mini_title
         , LEFT(community_members.description, 45) AS mini_desc
         , x.sum_del
         , x.sum_rec
         , x.latest_id
         , community_members.id AS url_id
         , community_members.admin_confed
      FROM community_members
    INNER
      JOIN ( SELECT url_id
                  , SUM(clicks_del) AS sum_del
                  , SUM(clicks_rec) AS sum_rec
                  , MAX(id) AS latest_id
               FROM traffic_xchange 
             GROUP
                 BY url_id
             HAVING sum_del < sum_rec ) AS x        
        ON x.url_id = community_members.id
     WHERE community_members.title IS NOT NULL 
       AND community_members.display = 'yes'
    ORDER 
        BY RAND() LIMIT 5;

    Anoox search engine volunteer

    www.anoox.com

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    glad it worked out okay for you
    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
  •