Duplicate records while retrieving using inner join in SQL server

Question

0.00/5 (No votes)

See more:

I am facing issues as I get records from SQL. The output of records was not right and numbers returned are much bigger than what I expected it to be.

This is my expected result :

Count   CustomerTaskTypeId   ScheduledDispatchedDateTime
852             7                      2019-08-20

but what I get is this :

Count   CustomerTaskTypeId   ScheduledDispatchedDateTime
4964         7                      2019-08-20

And what are the reasons why I get this large number of result?
Thanks for the help. :)

What I have tried:

Here is my query :

SQL

var query= $@"select CAST(CustomerAssignedTaskExec.ScheduledDispatchedDateTime as DATE) as 'Date',
                        CustomerTaskDetails.CustomerTaskTypeId, COUNT(*) as 'Count'
                        from CustomerAssignedTaskExec
                            inner join CustomerAssignedTask on CustomerAssignedTask.Id = CustomerAssignedTaskExec.CustomerAssignedTaskId
                            inner join CustomerTaskDetails on CustomerTaskDetails.Id = CustomerAssignedTaskExec.CustomerTaskDetailsId
                            inner join CustomerAssignedTaskItemStatus on CustomerAssignedTaskItemStatus.Id = CustomerAssignedTaskExec.AssignedTaskItemStatusId
                            inner join customers on customers.CustomerId = CustomerAssignedTask.CustomerId
                            where Customers.StoreId = @storeId and CustomerAssignedTask.TaskStatusId = @runningTaskStatusId
                            group by CAST(CustomerAssignedTaskExec.ScheduledDispatchedDateTime AS DATE), CustomerTaskTypeId, CustomerAssignedTaskItemStatus.Id";

Posted 29-Aug-19 15:46pm

Member 12688026

Updated 29-Aug-19 17:07pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2019-08-29T17:07:00

Without knowing the actual schema for the tables and the data we can only guess. On possibility is that you're joining the table using sibling paths thus resulting to duplicating records.

Perhaps the easiest way to quickly find out what's duplicating is to remove the grouping and including the keys of each table in the result. This way you can have a look at the data from which you get the false results and since you know the data and the structure of the tables it's easy to spot what are extra rows. After finding those, it's more easy to see the problem in the query, joins, missing table, extra table missing condition etc...

So have a try with something like

SQL

select CAST(CustomerAssignedTaskExec.ScheduledDispatchedDateTime as DATE) as 'Date',
   CustomerTaskDetails.CustomerTaskTypeId
   CustomerAssignedTaskExec.*,
   CustomerAssignedTask.*,
   CustomerTaskDetails.*,
   CustomerAssignedTaskItemStatus.*,
   customers.*
from CustomerAssignedTaskExec
   inner join CustomerAssignedTask on CustomerAssignedTask.Id = CustomerAssignedTaskExec.CustomerAssignedTaskId
   inner join CustomerTaskDetails on CustomerTaskDetails.Id = CustomerAssignedTaskExec.CustomerTaskDetailsId
   inner join CustomerAssignedTaskItemStatus on CustomerAssignedTaskItemStatus.Id = CustomerAssignedTaskExec.AssignedTaskItemStatusId
   inner join customers on customers.CustomerId = CustomerAssignedTask.CustomerId
where Customers.StoreId = @storeId 
and CustomerAssignedTask.TaskStatusId = @runningTaskStatusId