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?
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 ChongCommented:
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
....
0
Ryan ChongCommented:
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
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
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.
0
Ryan ChongCommented:
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
0
ste5anSenior DeveloperCommented:
You have Sales/SalesDetail and SalesReturn/SalesReturnDetail. Sounds like SalesDetail and SalesReturnDetail should correlate.
0
PortletPaulfreelancerCommented:
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
1

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.