How to group a table with another table which share the relationship in another table between them-CodePudding

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.

CodePudding user response：

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