multiple table joins with count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple table joins with count

Author

Topic

boybles
Starting Member

23 Posts

Posted - 2008-07-06 : 21:25:28

I'm trying to figure out how to join these tables together to get the following result. Please please help. Thanks!!!
Boybles

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-06 : 21:29:50

[code]
select e.EmployeeName, d.Departmentname, TotalMailReceived = count(*)
from mail m
inner join Employee e on m.MailTo = e.EmployeeID
inner join Department d on e.DepartmentID = d.DepartmentID
group by e.EmployeeName, d.Departmentname
[/code]

KH
[spoiler]Time is always against us[/spoiler]

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-07 : 06:05:44

Select EmployeeName, DepartmentName, Count(MailID) as TotalMailReceived
from Employee E, Department D, Mail M
where E.EmployeeId = M.MailTo
and D.DepartmentID = E.DepartmentID
group by EmployeeName, DepartmentName

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-07 : 06:09:18

quote:
Originally posted by EugeneLim11

Select EmployeeName, DepartmentName, Count(MailID) as TotalMailReceived
from Employee E, Department D, Mail M
where E.EmployeeId = M.MailTo
and D.DepartmentID = E.DepartmentID
group by EmployeeName, DepartmentName

No, use Khtan's example. The above example does not use T-SQL, it uses ansi-sql.

boybles
Starting Member

23 Posts

Posted - 2008-07-08 : 21:12:59

Khtan,
You rock! Once again, thank you for your help.
Boybles

boybles
Starting Member

23 Posts

Posted - 2009-05-06 : 04:02:09

Hey guys,

I'm having trouble when adding datetime conditions to this scenario.
If I expand my Mail table with a SentDate column:

Mail
----------
MailID (smallint)
MailName (varchar)
MailFrom (smallint)
MailTo (smallint)
SentDate (datetime)

And fill in the dates as such:

Mail
-------
1|Test Mail|1|2|1/1/2008
2|Today....|4|3|1/2/2008
3|About Joe|5|3|1/3/2008
4|Joe Rocks|2|1|2/1/2008
5|Tomorrow |2|3|2/2/2008
6|DailyNews|5|1|2/3/2008

I'd like to expand the SQL statement to get the following result:

EmployeeName|DepartmentName|TotalMailReceived
---------------------------------------------
Joe|Marketing|2
Jane|Marketing|0
Fred|Accounting|1
Frank|Sales|0
James|Accounting|0

I've tried the following:

select e.EmployeeName, d.Departmentname, TotalMailReceived = count(*)
from mail m
inner join Employee e on m.MailTo = e.EmployeeID
inner join Department d on e.DepartmentID = d.DepartmentID
where m.SentDate >= '2008-02-01'
and m.SentDate <= '2008-03-01'
group by e.EmployeeName, d.Department

But I still get the old result. What do I need to do to change the sql statement so that it will reflect the count accurately?

Thanks,
Boybles

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-06 : 06:13:50

[code]
DECLARE @Employee TABLE
(
EmployeeID smallint,
EmployeeName varchar(10),
DepartmentID smallint
)

DECLARE @Mail TABLE
(
MailID smallint,
MailName varchar(10),
MailFrom smallint,
MailTo smallint,
SentDate datetime
)

DECLARE @Department TABLE
(
DepartmentID smallint,
DepartmentName varchar(10)
)

INSERT INTO @Employee
SELECT 1, 'Joe', 1 UNION ALL
SELECT 2, 'Jane', 2 UNION ALL
SELECT 3, 'Fred', 3 UNION ALL
SELECT 4, 'Frank', 1 UNION ALL
SELECT 5, 'James', 3

INSERT INTO @Mail
SELECT 1, 'Test Mail', 1, 2, '2008-01-01' UNION ALL
SELECT 2, 'Today....', 4, 3, '2008-01-02' UNION ALL
SELECT 3, 'About Joe', 5, 3, '2008-01-03' UNION ALL
SELECT 4, 'Joe Rocks', 2, 1, '2008-01-02' UNION ALL
SELECT 5, 'Tomorrow' , 2, 3, '2008-02-02' UNION ALL
SELECT 6, 'DailyNews', 5, 1, '2008-02-03'

INSERT INTO @Department
SELECT 1, 'Sales' UNION ALL
SELECT 2, 'Marketing' UNION ALL
SELECT 3, 'Accounting'

SELECT e.EmployeeName, d.DepartmentName, TotalMailReceived = COUNT(m.MailTo)
FROM @Employee e
INNER JOIN @Department d ON e.DepartmentID = d.DepartmentID
left JOIN @Mail m ON e.EmployeeID = m.MailTo
AND m.SentDate >= '2008-02-01'
AND m.SentDate <= '2008-03-01'
GROUP BY e.EmployeeName, d.DepartmentName

/*
EmployeeName DepartmentName TotalMailReceived
------------ -------------- -----------------
Fred Accounting 1
James Accounting 0
Jane Marketing 0
Frank Sales 0
Joe Sales 1

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

boybles
Starting Member

23 Posts

Posted - 2009-05-06 : 10:32:19

Khatan,
That works wonderfully. What if I wanted to select only records where the TotalMailReceived count was greater than 0?
Thanks again,
Boybles

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-06 : 10:33:09

change LEFT JOIN to INNER JOIN

KH
[spoiler]Time is always against us[/spoiler]

boybles
Starting Member

23 Posts