Counting instances from several tables

[Johnsen]

Ok. I'm working in mySQL and basicly have 3 tables like this:

sites:
site_id
site_name
...

hits_in:
site_id
visitor_ip
timestamp

hits_out:
site_id
visitor_ip
timestamp

each time somebody goes from a registred site in the sites table to my site, site_id, visitor_ip and time stamp are registred in the hits_in table

and each time sombody goes the otherway from my site it's registred in the hits_out table.

Well what i want to do is to get a list over the sites with the according numbers of hits in and out, like:

site_id | site_name | hits_in | hits_out
      1 | somesite  |      23 |       34
      2 | anothersi |      22 |        2

Anybody knows hot to do this ?

thanks,, in advance

 

[MalcolmW]

First of all, dividing this problem into chunks - the site_id and site_name are easy to get. The two problems are getting the hits in and out neatly summarized and joined to the site_id and name. Derived or virtual tables are an easy way of doing that.

select sites.site_id
	,sites.site_name
	, inSum.countIn
	, outSum.countOut
from    sites
        inner join (select site_id, count(visitor_ip) countIn 
                    from hits_in 
                    group by site_id
                    ) inSum ON sites.site_id = inSum.site_id
        inner join (select site_id, count(visitor_ip) countOut 
                    from hits_out 
                    group by site_id
                    ) outSum ON sites.site_id = outSum.site_id

Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?