Welcome to WebmasterWorld Guest from 23.20.215.116

Forum Moderators: open

Message Too Old, No Replies

Mysql JOIN or Subselect?

Add the HITs column to my select

     
4:24 pm on Nov 6, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 2, 2004
posts: 187
votes: 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!
5:11 pm on Nov 6, 2011 (gmt 0)

New User

joined:Oct 26, 2011
posts: 13
votes: 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.
8:01 pm on Nov 6, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 2, 2004
posts: 187
votes: 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!
8:56 pm on Nov 6, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 2, 2004
posts: 187
votes: 0


Got it thanks!