poe Posted October 14, 2006 Share Posted October 14, 2006 i have 2 tableslinks (3 fields)- id - | - name - | - url -1 | msn | www.msn.com2 | google web page | www.google.com3 | cnn news | www.cnn.com4 | yahoo home page | www.yahoo.cometc...clicks (2 fields)- id - | - link_id -1 | 22 | 43 | 44 | 15 | 36 | 37 | 4etc... as someone clicks a link, the link _id is recorded in my clicks table.i now want to show the results by most popular link to least.ie.- rank - | - id - | - count - | - url - | - name -1 | 4 | 73 | www.yahoo.com | yahoo home page2 | 3 | 24 | www.cnn.com | cnn news3 | 2 | 18 | www.google.com | google web page.... etc.... $query = " SELECT link_id, COUNT(link_id) AS id_count FROM clicks GROUP BY link_id ORDER BY id_count DESC ";this will give me ...- rank - | - id - | - count - 1 | 4 | 732 | 3 | 243 | 2 | 18.... etc....but, how do i get my results to include the data from links table (url, name)$query = " SELECT clicks.link_id, COUNT(clicks.link_id) AS id_count, links.url, links.name FROM clicks, links GROUP BY link_id ORDER BY id_count DESC ";if i use this query, my count is way off as if it takes the count multiplied by number of records in the links table Quote Link to comment Share on other sites More sharing options...
Barand Posted October 15, 2006 Share Posted October 15, 2006 You need to specify the join condition for the tables otherwise, as you have noticed, each record in one table is joined with every record in the other table[code]SELECT clicks.link_id, COUNT(clicks.link_id) AS id_count, links.url, links.nameFROM clicks INNER JOIN links ON links.id = clicks.link_idGROUP BY link_idORDER BY id_count DESC[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.