SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  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,322
    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,322
    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,322
    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
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    It gives Error message that:

    Unknown table 'traffic_xchange' in field list

    And just to be sure I issued:
    DESCRIBE traffic_xchange;

    and it produced the description of Table traffic_xchange, so there is no misspelling.

    Also FYI, I replaced in your command:
    traffic_xchange.id
    with
    x.id

    and got same Error:
    Unknown column 'x.id' in 'field list'




    Quote Originally Posted by r937 View Post
    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;

    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    Also FYI, I replaced in your command:
    traffic_xchange.id
    with
    x.id
    ah yes, sorry

    okay, just remove it from the SELECT clause, because you don't need it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Except that we absolutely need the traffic_xchange.id since we use this id to keep track of how many free Clicks we have delivered to a member for the unique clicks that they have delivered to us. To be exact each row in traffic_xchange holds the info about each community members URL from which URL they have sent us for a given day and how many clicks we have sent them.

    Also there is another problem with your code, well actually my code too, that is we need to take into account the SUM of clicks that they have sent us from a given URL for all the dates versus the SUM of clicks that we have send them AND if SUM of our clicks sent them is less than SUM of the clicks they have sent us then and only then should we pull up their info from the community Table and list them under the Free Ads to stand to get more free clicks from us. But right now, both my original code, and your suggested code, are not dealing with SUMs!

    As I said this is a complex MySQL command


    Quote Originally Posted by r937 View Post
    ah yes, sorry

    okay, just remove it from the SELECT clause, because you don't need it

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    But right now, both my original code, and your suggested code, are not dealing with SUMs!
    isn't it interesting that you neglected to mention this until now

    okay, each row in community_members can have multiple related rows in traffic_xchange, and you want all of those traffic_xchange rows to be summed up, right?

    so, out of the multiple traffic_xchange rows for a given community member, which traffic_xchange.id do you want? they're all unique, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    isn't it interesting that you neglected to mention this until now

    *** Well as I wrote I realized that shortcoming (Error) as I was looking at my code and your code, trying to get the desired results.
    Anyway.


    okay, each row in community_members can have multiple related rows in traffic_xchange, and you want all of those traffic_xchange rows to be summed up, right?

    *** Yes to both.

    so, out of the multiple traffic_xchange rows for a given community member, which traffic_xchange.id do you want? they're all unique, right?
    *** The last traffic_xchange.id can be used for the purpose of updating the number of free clicks that we sent them, which is clicks_del. Because what we care are is to only display their Ads for free, which means their URL from the community Table, for as long as the SUM(clicks_del) is less than SUM(clicks_rec) which is the number of clicks they sent us.

    ThanX,

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    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"

  13. #13
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    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
  •