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 Keyurl: VARCHARdisplay: 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 tableclicks_rec: which indicates how many clicks they we have received from the community memberclicks_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,

r937
—
2011-02-11T11:24:20Z —
#2

your problem is the GROUP BY clause

why did you put that in your query?

remove it and see what happens

WorldNews
—
2011-02-11T15:40:08Z —
#3

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,

r937 said:

your problem is the GROUP BY clause

why did you put that in your query?

remove it and see what happens

r937
—
2011-02-11T15:43:58Z —
#4

WorldNews said:

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

WorldNews
—
2011-02-11T16:02:03Z —
#5

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.

r937 said:

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

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

WorldNews
—
2011-02-11T18:58:20Z —
#9

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

r937 said:

ah yes, sorry

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

r937
—
2011-02-11T21:21:23Z —
#10

WorldNews said:

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?

WorldNews
—
2011-02-12T00:45:21Z —
#11

r937 said:

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.