I figured somebody might know the answer from the 'double join' side of things...
Here's a couple of test tables with inserts:
create table #logs
(
log_id int,
id int,
log_date datetime
)
INSERT INTO #Logs SELECT 27824224 as Log_ID, 94 as ID, '2012-12-20 20:39:07.433' as Log_Date
INSERT INTO #Logs SELECT 27824212 as Log_ID, 151 as ID, '2012-12-20 20:38:57.130' as Log_Date
INSERT INTO #Logs SELECT 28833771 as Log_ID, 117 as ID, '2013-01-30 16:36:54.047' as Log_Date
INSERT INTO #Logs SELECT 28833854 as Log_ID, 119 as ID, '2013-01-30 16:38:00.673' as Log_Date
INSERT INTO #Logs SELECT 28833690 as Log_ID, 121 as ID, '2013-01-30 16:35:23.073' as Log_Date
INSERT INTO #Logs SELECT 28833733 as Log_ID, 121 as ID, '2013-01-30 16:36:02.000' as Log_Date
INSERT INTO #Logs SELECT 28833871 as Log_ID, 121 as ID, '2013-01-30 16:38:13.247' as Log_Date
INSERT INTO #Logs SELECT 28833900 as Log_ID, 121 as ID, '2013-01-30 16:38:50.350' as Log_Date
INSERT INTO #Logs SELECT 28833970 as Log_ID, 121 as ID, '2013-01-30 16:40:10.247' as Log_Date
INSERT INTO #Logs SELECT 28834134 as Log_ID, 121 as ID, '2013-01-30 16:42:20.503' as Log_Date
INSERT INTO #Logs SELECT 28834217 as Log_ID, 121 as ID, '2013-01-30 16:43:37.447' as Log_Date
INSERT INTO #Logs SELECT 28834329 as Log_ID, 121 as ID, '2013-01-30 16:45:01.253' as Log_Date
INSERT INTO #Logs SELECT 28834465 as Log_ID, 121 as ID, '2013-01-30 16:46:39.210' as Log_Date
INSERT INTO #Logs SELECT 28834544 as Log_ID, 121 as ID, '2013-01-30 16:47:47.623' as Log_Date
INSERT INTO #Logs SELECT 28834577 as Log_ID, 121 as ID, '2013-01-30 16:48:13.480' as Log_Date
INSERT INTO #Logs SELECT 28834589 as Log_ID, 121 as ID, '2013-01-30 16:48:21.643' as Log_Date
INSERT INTO #Logs SELECT 28834639 as Log_ID, 121 as ID, '2013-01-30 16:48:49.837' as Log_Date
INSERT INTO #Logs SELECT 28834679 as Log_ID, 122 as ID, '2013-01-30 16:49:21.230' as Log_Date
create table #Items
(
id int,
title varchar(50)
)
INSERT INTO #Items SELECT 94 as ID, 'Badger' as Title
INSERT INTO #Items SELECT 151 as ID, 'Youngs' as Title
INSERT INTO #Items SELECT 117 as ID, 'Fullers' as Title
INSERT INTO #Items SELECT 119 as ID, 'Neame' as Title
INSERT INTO #Items SELECT 121 as ID, 'Batemans' as Title
INSERT INTO #Items SELECT 122 as ID, 'Adnams' as Title
Now If I run this:
SELECT
t.ID,
t.Title,
COUNT(l.Log_ID) as c1
FROM #Items t
JOIN #logs l on l.ID = t.id
GROUP BY t.id, t.title
It works fine,
If I run this:
SELECT
t.ID,
t.Title,
COUNT(l.Log_ID) as c1,
COUNT(la.Log_ID) as c2
FROM #Items t
JOIN #logs l on l.ID = t.id
JOIN #logs la on la.ID = t.id
GROUP BY t.id, t.title
It still works, as in it doesn't throw an error but the data isn't as intended, I'd expect another column duplicating the results of column C1, however, I've created some kind of monster which produces a count of 169 for 'Batemans' rather than 13.
There's something obvious I'm not spotting here which is kind of annoying.