Joins with 3 tables.

[ssv45324]

This is my requirement.

I have a table A which has names, a table B which has the statistics of the number of users visting the sites(whose names are in A) and a table C which has the company information. I need to write a SQL statement to get all the names in tableA and the number of times it was visited from table B. (Even if there is no visit to a site, i should get the name and the count as 0). This is the SQL i have written, but am not getting all the names from table A. Any help would be appreciated.


select B.name, count(*) from A left join B on A.name = B.name left JOIN C ON B.id = A.id and C.user_id IN (select user.user_id from user where company_id=115) where B.init_time between @startdate AND @enddate GROUP BY B.name

 

[bborissov]

?????LEFT JOIN C ON B.Id = A.Id??????????????????????
WOW

Can you post some example data to see. From that select you used nothing is clear. How you join A and B by Name? Are you sure that there are no two persons with the same name?

How you join tableC?


Borislav Borissov

 

[gmmastros]

Your query doesn't make sense because the join condition for C includes a condition between A and B left JOIN C ON B.id = A.id and ....

Another problem you have is that you want all the names in A, but you select b.name instead of a.name, and you group on b.name instead of a.name.

I threw this together for you. It's not complete because I don't know how C in involved in the query. If you run this, it should provide all the A.Names and the Counts (including the zero's). Like I said, it's not a complete solution, but hopefully it will be a nudge in the right direction.

Select A.Name,
       RecordCounts.CountOfMatchingNames
From   A
       Inner Join	
           (
            select A.name, 
                   count(B.Name) As CountOfMatchingNames
            From   A 
                   left join B 
                       on  A.name = B.name 
                       And B.Init_Time Between @StartDate And @EndDate
            Group  By A.Name
           ) As RecordCounts
       On A.Name = RecordCounts.Name

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom