homepage Welcome to WebmasterWorld Guest from 23.20.61.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Mysql JOIN or Subselect?
Add the HITs column to my select
HeadBut

10+ Year Member



 
Msg#: 4384101 posted 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#: 4384101 posted 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

10+ Year Member



 
Msg#: 4384101 posted 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

10+ Year Member



 
Msg#: 4384101 posted 8:56 pm on Nov 6, 2011 (gmt 0)

Got it thanks!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved