CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Ravi Khandelwal
Ravi Khandelwal

Reputation: 3

Query returning duplicates

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PrintQuickBill](@BillNo int)
AS
SELECT      A.BillNo, 
            A.BillDate, 
            A.CustomerName, 
            A.Address, 
            A.CustomerId, 
            A.BillLaborAmt, 
            A.BillPartAmt, 
            A.ServTaxAmt, 
            A.VatAmt,
            A.BillNetAmt,
FROM        dbo.tblQuickBillMain A
INNER JOIN  [dbo].tblQuickBillLabor L
        ON  A.BillNo = L.BillNo
INNER JOIN  [dbo].tblQuickBillParts P
        ON  A.BillNo = P.BillNo
CROSS JOIN  dbo.CompanyInfo
WHERE       A.BillNo=@BillNo
ORDER BY    CustomerName

This SELECT statement returns the same result multiple times. If I have 1 record the result shows it 3 times. If I pass @BillNo=1 to the procedure, it should return only one row but it returns 3 rows, which are identical.

Upvotes: 0

Views: 883

Answers (2)

trincot
trincot

Reputation: 349946

The duplicates result from one of the following three situations (or a combination of them):

  • When CompanyInfo has more than one record (like 3 records).
  • When tblQuickBillLabor.BillNo has duplicates;
  • When tblQuickBillParts.BillNo has duplicates;

You don't see the difference between some results because you don't show any of the data from those other tables in the SELECT list. If you would include columns from the other tables in your SELECT list as well, you would notice that those columns would have different values in the results where the first bunch of columns would have duplicates.

But as you do not use values from the table CompanyInfo at all in your query, you should any way remove the CROSS JOIN CompanyInfo part of your query: it serves no purpose unless you actually include columns from that table in your SELECT list. Removing this might already completely solve your problem.

However, the table name tblQuickBillParts suggests strongly that you will have multiple records for the same BillNo in that table, otherwise you would not call it Parts in plural. The same might be true for the table tblQuickBillLabor.

To deal with these potential problems, caused by the join with tblQuickBillLabor.BillNo and tblQuickBillParts.BillNo, see the following solutions:

Solution A: Do not join tables at all

As you are only selecting values from tblQuickBillMain, you might eventually not need to join the other tables at all, and just write:

SELECT   BillNo, 
         BillDate, 
         CustomerName, 
         Address, 
         CustomerId, 
         BillLaborAmt, 
         BillPartAmt, 
         ServTaxAmt, 
         VatAmt,
         BillNetAmt,
FROM     dbo.tblQuickBillMain
WHERE    BillNo=@BillNo
ORDER BY CustomerName

This way you will not get duplicates, but you will also get records for which there is no corresponding record in tblQuickBillLabor.BillNo or tblQuickBillParts.BillNo. If that match is a requirement, then check the next solution.

Solution B: Replace joins with sub-queries

If the reason for the joins with tblQuickBillLabor.BillNo and tblQuickBillParts.BillNo is that you want to be sure there is at least one matching record in those tables, then use a WHERE condition instead in the form of BillNo IN (sub query): 

SELECT   BillNo, 
         BillDate, 
         CustomerName, 
         Address, 
         CustomerId, 
         BillLaborAmt, 
         BillPartAmt, 
         ServTaxAmt, 
         VatAmt,
         BillNetAmt,
FROM     dbo.tblQuickBillMain
WHERE    BillNo=@BillNo
AND      BillNo IN (SELECT BillNo FROM [dbo].tblQuickBillLabor)
AND      BillNo IN (SELECT BillNo FROM [dbo].tblQuickBillParts)
ORDER BY CustomerName

Note that with this SQL statement you do not need to alias your tables with A, P, L as there is no ambiguity.

Solution C: The Bad SELECT DISTINCT

A simple, but lazy and not advisable way to remove duplicates from the result set is to add the word DISTINCT right after the word SELECT.

With this "solution" you don't really get to the source of the problem; you just ignore it and ask for a non-duplicate result set. So, I would really suggest to go for solution A or B, whichever fits best to your requirements.

Upvotes: 2

Arish Shaikh
Arish Shaikh

Reputation: 11

Your query has a cross join to table CompanyInfo. And it is returning three rows, because the table in cross join (CompanyInfo) has three rows. You are not using any column from CompanyInfo, so I don't think that there is any need of that cross join.

Upvotes: 0

Related Questions