Welcome to WebmasterWorld Guest from 54.221.119.45

Forum Moderators: open

Message Too Old, No Replies

Mysql JOIN or Subselect?

Add the HITs column to my select

     

HeadBut

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

10+ Year Member



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

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

8:01 pm on Nov 6, 2011 (gmt 0)

10+ Year Member



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

8:56 pm on Nov 6, 2011 (gmt 0)

10+ Year Member



Got it thanks!
 

Featured Threads

Hot Threads This Week

Hot Threads This Month