| Mysql JOIN or Subselect?
Add the HITs column to my select |
HeadBut
msg:4384103 | 4:24 pm on Nov 6, 2011 (gmt 0) |
Not sure how to add another table to my select. I have a 'pages' table and a 'hits' table. I'd like to add the number of hits for each page row something like:
SELECT pages.pagenum, pagedescription, (select COUNT(*) as Views from hits WHERE pagenum = pages.pagenum ) as Views
from pages
WHERE 1 = 1 ORDER BY Views
Clearly I don't know how to do this, thanks for your help!
|
Jstanfield
msg:4384113 | 5:11 pm on Nov 6, 2011 (gmt 0) |
All pages have at least one hit?
SELECT pages.pagenum, pagedescription, COUNT(1) as hit_count
from pages
INNER join hits on hits.pagenum = pages.pagenum
group by pages.pagenum
Some pages have not been hit?
SELECT pages.pagenum, pagedescription, COUNT(1) as hit_count
from pages
LEFT join hits on hits.pagenum = pages.pagenum
group by pages.pagenum
Difference is: pages with no hits will be filtered out of the inner join, but will be included in the left join.
|
HeadBut
msg:4384155 | 8:01 pm on Nov 6, 2011 (gmt 0) |
That only gave me 1 result. So I must be leaving out something important.
The hits table has multiple entries for each page and I am trying to count them and include the result for each page.
I want to get an output like:
pagenum pagedescription hits
4567 Pagetext... 456
4568 anotherPagetext 435
The hits table looks like this:
IPAddress pagenum
10.10.10.10 4567
12.12.12.12 4567
13.13.13.13 4568
etc....
Not sure if it maters but I am also doing a join from another table like:
JOIN users on users.AccountNum = pages.ActNum
Thanks a TON!
|
HeadBut
msg:4384175 | 8:56 pm on Nov 6, 2011 (gmt 0) |
Got it thanks!
|
|
|
