Currently, I have two tables Table one - list of URLS (each url appears only once) Table two - list of URLS (each url appears many times - some don't appear at all) Basically, I want to rank each of the URLs from table one based upon the number of times they appear in table two. What would be the easier/fastest/most efficient way to accomplish this?
why do u have two tables if they contain the same thing ? what you can do is just have 1 table and another field called (# of recurrence) then you can just display using ASC or DESC depending on what you want .
$q = "SELECT COUNT(url) as mycount, url, otherfield FROM tableurl GROUP BY url ORDER BY mycount DESC "; eliminate the first table. your idea of one to many relationship will be useful only if the first table has relation to any other table than your second table
The first table is a list of everyone who has registered for my site. The second table tracks the lastest referrers, so i can't do what commandos said. brealms - could you explain that a bit more?
You can , by default all have 0 and then you can do what brealmz said or instead of having duplicate rows , just another field that say how many time it was refered : Table : url :: number of visit google.com ; 15 yahoo.com ; 9 newurl.com ; 0
try this SELECT COUNT( table1.url ) AS mycount, table1.url AS urls FROM table1 LEFT JOIN table2 ON table1.url = table2.url GROUP BY table1.url ORDER BY mycount DESC however if 1 entry in table1 / 1 entry in table2 = 1 1 entry in table1 / 0 entry in table2 = 1 1 entry in table1 / 5 entry in table2 = 5