MySQl Join Help Needed!

[mousey182]

Hey Guys,

Thanks for taking the time to read this, I have the following, rather complicated queries that I am trying to join, and I've spent the past two days attempting to do so, but had no luck so far so any help would be very much appreciated.

I have the following query which queries a database and finds the links that have the highest average view count (the same link_id's may be visited from different locations, hence the weighting column)...

SELECT d_link_id, AVG(score) as 'avg' 
FROM
         ( SELECT d_link_id, (frequency*weighting) as 'score' 
           FROM users_to_links
           WHERE s_user_id = $user_id) a
GROUP BY d_link_id
ORDER BY avg DESC

This query runs fine and gives me results similar to the following:

d_link_id---------------avg
0------------------------4.0000
23-----------------------3.75

I also have a table called links_to_keywords that associates keywords to the links. The links have multiple keywords associated with them, and so Im trying to create a query that produces results similar to the following:

s_link_id---------d_key_id----------avg
0----------------------5---------------4.00000
0----------------------6---------------4.00000
0----------------------12--------------4.00000

etc etc

I hope this makes sense!!! I'm off to cry in a corner for a while as Im quite possibly losing my mind!

Thanks in advance guys!

Matt