I need to generate a rather very complex MySQL list and I am having problems getting the right results.
we have 2 Tables:
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
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?