I have three tables like the following:
Table A
|IDTableA | IDTableB
1 1
2 1
3 2
4 2
5 3
6 4
7 1
8 1
Table B
|IDTableB | IDTableC
1 1
2 1
3 2
4 2
Table C
| IDTableC |
1
2
Table D
|IDTableD | IDTableA
1 1
2 2
3 3
4 4
What I'm trying to get is the id in Table C with the maximum number of items in table A (which should be the ID 1 in table C)
This is what I've done until now:
Select tableA.IDTableA, COUNT(*) as items, TableB.IDTableB, TableC.IDTableC from TableA
INNER JOIN TableB ON TableB.IDTableB = TableA.IDTableB
INNER JOIN TableC ON TableC.IDTableC = TableB.IDTableC
GROUP BY tableA.IDTableA, TableB.IDTableB, TableC.IDTableC
ORDER BY items DESC
It it always groups the result by the id from tableB and not the ID from tableC so I can't use the COUNT(*) items to get the highest number of items.
What I'm trying to get is the id in Table C with the maximum number of items in table A
If that is your goal, then you need to only group by the items in Table C and count the items in Table A.
Here's a working example of how this would work.
First, I reproduced your table setup like so:
DECLARE @TableA TABLE (IDTableA int, IDTableB int)
DECLARE @TableB TABLE (IDTableB int, IDTableC int)
DECLARE @TableC TABLE (IDTableC int)
INSERT INTO @TableA (IDTableA, IDTableB) VALUES
(1,1),
(2,1),
(3,2),
(4,2),
(5,3),
(6,4),
(7,1),
(8,1)
INSERT INTO @TableB (IDTableB, IDTableC) VALUES
(1,1),
(2,1),
(3,2),
(4,2)
INSERT INTO @TableC (IDTableC) VALUES
(1),
(2)
And this would be the query that gets what you want:
SELECT COUNT(tableA.IDTableA) AS items, TableC.IDTableC from @TableA tableA
INNER JOIN @TableB tableB ON TableB.IDTableB = TableA.IDTableB
INNER JOIN @TableC tableC ON TableC.IDTableC = TableB.IDTableC
GROUP BY TableC.IDTableC
ORDER BY items DESC