Left Join? Need to combine a "log table" with an "accounts table"

[ProInfo]

Greeting from Indiana!

This is what I am working with, I have two tables. One is an accounts table, one is a visitors table where records are added as a person visits.

I believe I need to use a LEFT JOIN, but I am not sure how to do it. I have tried creating one and I get errors that I don't understand.

My Accounts Query: lib_code is the account name
SELECT lib_code FROM dbo.lib_info ORDER BY lib_code
ie.e
----------------
ATC Computers
Bill's BBQ
Dirks Laundry
Z's Car Sales


The Visits Query: This one counts all the records and returns the account name as lib_id based on the last 5 days of visits.

Select lib_id,count(*) as cnt from dbo.lib_visits
WHERE lib_time >= dateadd(day,datediff(day,5,GETDATE()),0)
AND
lib_time < dateadd(day,datediff(day,0,GETDATE()),0)
GROUP BY lib_id;

This one returns
ATC Computers | 10
Bill's BBQ | 5
Dirks Laundry | 6

Notice from the list above Z's car sales is off the list. This is because there are no visits from them in the past 5 days. However, I need them to still show up in the list, as a null or 0.

ATC Computers | 10
Bill's BBQ | 5
Dirks Laundry | 6
Z's Car Sales | 0/Null

So I think I need to left join the first query with the second query, but I don't know how or exactly what I am doing.

Any help would be appreciated.

Thanks.

 

[PWise]

SELECT lib_code 
FROM dbo.lib_info 
left join (
          Select lib_id,count(*) as cnt 
          from dbo.lib_visits    
          WHERE  lib_time >= dateadd(day,datediff                 ( day,5,GETDATE)),0)
       AND 
       lib_time < dateadd(day,datediff(day,0,GETDATE()),0)
       GROUP BY lib_id
       )visits
on visits.lib_id=lib_info.lib_id

 

[PWise]

sorry sb

SELECT lib_info.lib_code ,cnt 
FROM dbo.lib_info 
left join (
          Select lib_id,count(*) as cnt 
          from dbo.lib_visits    
          WHERE  lib_time >= dateadd(day,datediff                 ( day,5,GETDATE)),0)
       AND 
       lib_time < dateadd(day,datediff(day,0,GETDATE()),0)
       GROUP BY lib_id
       )visits
on visits.lib_id=lib_info.lib_id

 

[ProInfo]

Awesome, thank you very much for the quick reply. I can finally get back to work!!!

I did have to fix one thing at the bottom because I got an invalid column name at the very bottom "on visits.lib_id=lib_info.lib_id" should have been "on visits.lib_id=lib_info.lib_code". But I figured that part out.

I wish I would have saved my attempt to left join, because your code looks very similar to mine but there had to have been a subtle difference.

Thanks again!!!