Reason for SQL Query records repeating in my example

Hello there,

I have this query which I am trying to return the returned quantity from sales receipt for a month. But for some reason the records are repeating several times. Can somebody please help me to pin point the casue of it.

cheers
Zolf

[code]SELECT
    dbo.Receipt.id,
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptType,
    dbo.Receipt.receiptDate,
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.SaleReturnDetail.quantity
FROM
    dbo.InvoiceReceipt
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.InvoiceReceipt.id = dbo.Receipt.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.InvoiceReceipt.id = dbo.Sale.invoiceReceiptId)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.Sale.id = dbo.SaleReturn.saleId)
INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleReturnDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23' ;[/code]

Open in new window

ee3.gif
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
for quick solution, try distinct or group by...

SELECT distinct
    dbo.Receipt.id,
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptType,
    dbo.Receipt.receiptDate,
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.SaleReturnDetail.quantity
FROM
    dbo.InvoiceReceipt
INNER JOIN
    dbo.Receipt
ON
....
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
or group by...

SELECT
    dbo.Receipt.id,
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptType,
    dbo.Receipt.receiptDate,
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.SaleReturnDetail.quantity
FROM
....

Group By dbo.Receipt.id,
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptType,
    dbo.Receipt.receiptDate,
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.SaleReturnDetail.quantity
zolfAuthor Commented:
But I want to know the reason for so many repeating records,so I can fine tune my query by removing the cause.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in your database structure, I believe there is a one to many relationship. that's why you design your database and how your data is being stored. but always remember that if you're joining more and more tables, there is a "multiple" effect in your records.

if you selecting more fields from those tables, you will see each record is different/ distinct with each other, BUT if you only selected several fields, the records that being returned will look like "duplicated".

I will try find an article to demonstrate this later.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try see the explanation from this example.

you have the following table structures:

create table users(
  id int,
  first_name varchar(30),
  last_name varchar(30)
  );
 create table jobs(
  id int,
   job_name varchar(30)
 );
 create table user_jobs(
   id int,
   user_id int,
   job_id int,
   start_from datetime
 );
insert into users values (1, 'Mary', 'Shirley');
insert into users values (2, 'Jane', 'Doe');
insert into users values (3, 'John', 'Roe');
insert into users values (4, 'David', 'Williams');
insert into users values (5, 'Dave', 'Joe');
insert into jobs values (0, 'web developer');
insert into jobs values (1, 'barista');
insert into jobs values (2, 'teacher');
insert into jobs values (3, 'astronaut');
insert into jobs values (4, 'farmer');
insert into user_jobs values (1, 1, 1, '25 Jan 2005');
insert into user_jobs values (2, 1, 2, '01 Jan 2008');
insert into user_jobs values (3, 2, 0, '19 Dec 2009');
insert into user_jobs values (4, 2, 4, '13 May 2010');
insert into user_jobs values (5, 3, 2, '22 Feb 2014');
insert into user_jobs values (6, 1, 3, '06 Jan 2012');
insert into user_jobs values (7, 5, 1, '12 Aug 2004');

Open in new window


then let's say we want to find out who previously was employed (so the employment history was stored in table: user_jobs)

you have this query which seems duplicate:
select a.id, a.first_name, a.last_name
from users a
inner join user_jobs b on b.user_id = a.id
inner join jobs c on c.id = b.job_id
order by a.id, a.first_name, a.last_name, b.start_from, c.job_name

Open in new window

records look duplicated
but when you have this, it seems that the records are unique:

select a.id, a.first_name, a.last_name, c.job_name, b.start_from
from users a
inner join user_jobs b on b.user_id = a.id
inner join jobs c on c.id = b.job_id
order by a.id, a.first_name, a.last_name, b.start_from, c.job_name

Open in new window

records look ok
To eliminate the first query's problem, we will need to use either distinct or group by clauses as mentioned.

1. using group by

select a.id, a.first_name, a.last_name
from users a
inner join user_jobs b on b.user_id = a.id
inner join jobs c on c.id = b.job_id
group by a.id, a.first_name, a.last_name
order by 1, 2, 3

Open in new window

using group by
2. using distinct
select distinct a.id, a.first_name, a.last_name
from users a
inner join user_jobs b on b.user_id = a.id
inner join jobs c on c.id = b.job_id
order by 1, 2, 3

Open in new window

using distinct
I hope this is clear enough
ste5anSenior DeveloperCommented:
You have Sales/SalesDetail and SalesReturn/SalesReturnDetail. Sounds like SalesDetail and SalesReturnDetail should correlate.
PortletPaulEE Topic AdvisorCommented:
In this particular query, you are joining several "detail" tables so that you can get this data:

    dbo.Tafsil.description,
    dbo.SaleReturnDetail.quantity

in those "detail" tables will be many rows for each sale (one row per product probably)

It is sometimes tempting to add DISTINCT into a query to suppress unwanted repetition (often referred to as duplication) but it is far better to understand WHY you get that repetition. You have asked us a question we cannot answer literally because we don't have access to your tables and data, however to discover which table(s) cause thsi isn't hard.

start with this:
SELECT
      count(*)
FROM dbo.InvoiceReceipt
INNER JOIN dbo.Receipt
      ON dbo.Receipt.branchId = dbo.Branch.id
WHERE dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'

AND dbo.Receipt.id = 2752302;

Open in new window

Then progressively add more joined tables one by one. At some point you will start to see the count increase and as soon as it does you have discovered a "one to many" relationship in the data that has expanded the rows.

Now, armed with WHICH TABLE(S) cause the problem, solve it. A common method is to use a subquery that groups the information, and join that subquery rather than the source tables.

Perhaps read this: Why I Hate DISTINCT

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.