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,