SQL query with 3 tables returns duplicate rows-CodePudding

Hopefully this won't get flagged as duplicate. I am struggling to understand the return of duplicate rows when more than 2 tables are in query. I have read a number of similar questions and posts online but can't wrap my head around the logic. I am hoping that if someone could explain with my specific use case, it should finally sink in.

I have 3 tables

[dbo].[Branch](
    [ContractorCode] [int] NOT NULL,
    [BranchNumber] [int] NOT NULL,
    [BranchName] [varchar](50) NOT NULL,
    ...
    [HealthBoardID] [int] NULL

[dbo].[OutstandingCount](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EnteredOn] [datetime2](7) NOT NULL,    
    [Branch] [varchar](50) NOT NULL,
    [productGroup] [int] NULL,  
    [Product] [nvarchar](100) NOT NULL, 
    ...
    [Satisfied] [bit] NOT NULL

[dbo].[TargetLineCount](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Branch] [int] NOT NULL,
    [ProductGroup] [nvarchar](10) NOT NULL,
    [TargetCount] [int] NOT NULL,
    ...

There is a 1:M relation from Branch table to OutstandingCount table and there is a 1:1 relation from Branch table to TargetLineCount table

What I need to return is by branch how many items are still to count so how many rows in OutstandingCount with Satisfied = 0 how many items are added to the count each day, this is decided by the branch and product group.

This is my query

SELECT b.BranchName,
SUM(CASE WHEN o.ProductGroup = 2 THEN 1 ELSE 0 END) AS [NHS to count],
MAX(CASE WHEN t.ProductGroup = 'DISP' THEN t.TargetCount ELSE 0 END) AS [NHS daily count],
SUM(CASE WHEN o.ProductGroup = 1 THEN 1 ELSE 0 END) AS [OTC to count],
MAX(CASE WHEN t.ProductGroup = 'OTC' THEN t.TargetCount ELSE 0 END) AS [OTC daily count]
FROM OutstandingCount o
INNER JOIN Branch b ON o.Branch = b.BranchName
INNER JOIN TargetLineCount t ON t.Branch = b.BranchNumber
WHERE o.Satisfied = 0 AND o.EnteredOn < '2022-07-01'
GROUP BY b.BranchNumber,b.BranchName
ORDER BY b.BranchNumber

The target counts return correct, e.g. for branch 1 the [NHS/OTC daily count] is 7 and branch 2 it is 8. However the [NHS/OTC to count] figures are doubled, e.g. branch 1 should be 65 but is 130, branch 2 should be 7 but is 14.

As I say, it is all coming from my lack of understanding how to treat multiple table joins. If I request either of the data separately, I get the desired result, it is only when I try and get all the information in a single query I struggle.

Sample data and output:

Branch:

ContractorCode	BranchNumber	BranchName
1234	1	Branch1
5465	2	Branch2

OutstandingCount:

Id	EnteredOn	Branch	ProductGroup	Product	Satisfied
9990	2022-07-01	Branch1	1	pr55	0
9991	2022-07-01	Branch1	1	pr60	0
9992	2022-07-02	Branch1	2	pr78	0
9993	2022-07-01	Branch2	1	pr55	0
9995	2022-07-02	Branch2	1	pr78	0
9996	2022-07-02	Branch2	2	pr30	0
9998	2022-07-03	Branch2	2	pr55	1

TargetLineCount:

ID	Branch	ProductGroup	Target
1	1	PG1	7
2	1	PG2	10
4	2	PG1	8
5	2	PG2	8

Desired Result would be:

Branch	NHS To Count	NHS Daily Count	OTC To Count	OTC Daily Count
Branch1	1	10	2	7
Branch2	1	8	2	8

In explanation - Branch1 has no satisfied items so we have 2 from ProductGroup1 to count and 1 from ProductGroup2. Branch2 has 1 satisfied so we count 2 for ProductGroup1 and 1 for ProductGroup2. The to count figures from the TargetLineCount table are a 1:1 so there is only one value for Branch1 for ProductGroup1 and one value for Branch1 for ProductGroup2.

Solution:

Thanks to the help from Dragon, my specific issue needed me to add a further table for joining and then extend the queries to use that table as well

INNER JOIN ProductGroup p ON p.Branch = b.ContractorCode AND p.ProdGroupID = o.productGroup

INNER JOIN TargetLineCount t ON t.Branch = b.BranchNumber AND t.ProductGroup = p.Description

CodePudding user response：

Ok, I see what the issue is.

It is with your join

INNER JOIN TargetLineCount t ON t.Branch = b.BranchNumber

It should be like this:

INNER JOIN TargetLineCount t ON t.Branch = b.BranchNumber AND o.ProductGroup = t.ProductGroup

The only thing is the Product Group connection between TargetLineCount and OutstandingCount doesn't appear to be shown properly in your example as one is a varchar and the other is an int?