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:

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,

your problem is the GROUP BY clause

why did you put that in your query?

remove it and see what happens

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,

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

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.

you should have mentioned this earlier

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;

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’

ah yes, sorry

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

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 :slight_smile:

isn’t it interesting that you neglected to mention this until now :smiley:

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?

*** 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,

SELECT community_members.url
     , LEFT(community_members.title, 25) AS mini_title
     , LEFT(community_members.description, 45) AS mini_desc
     , [COLOR="Blue"]x.sum_del
     , x.sum_rec
     , x.latest_id[/COLOR]
     , community_members.id AS url_id
     , community_members.admin_confed
  FROM community_members
INNER
  JOIN ( SELECT url_id
              , [COLOR="blue"]SUM(clicks_del) AS sum_del
              , SUM(clicks_rec) AS sum_rec
              , MAX(id) AS latest_id[/COLOR]
           FROM traffic_xchange 
         GROUP
             BY url_id
         [COLOR="Blue"]HAVING sum_del < sum_rec[/COLOR] ) 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; 

My hat off to you :slight_smile:
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 :slight_smile:
OTN, I look and look at your code and cannot figure what the hek it is doing :frowning:

glad it worked out okay for you :slight_smile: