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?
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.
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;
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!
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?
*** 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.
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
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