How to avoid duplicate records while retrieving using Inner Join in SQL Server

Question

Here is my Store Procedure for printing purpose. I am facing issue as i get records twice as they exist due to commented join. I want to get output as mentioned below in question. I am a novice to DB. Help in this regard will be appreciated.

DECLARE @path AS VARCHAR(100)
SET @path = N'~/BillAttachments/166/'
DECLARE @billID AS INT
SET @billID = 166

SELECT bh.billID,bh.statusID,bh.modifiedOn,bs.statusName
FROM dbo.eInvoice_tbl_billHistory AS bh
--INNER JOIN dbo.eInvoice_tbl_billAttachment AS ba ON bh.billID = ba.billID
--INNER JOIN dbo.eInvoice_tbl_billAttachment AS ba ON ba.billID = bh.billID
INNER JOIN dbo.eInvoice_tbl_billStatus AS bs ON bh.statusID = bs.statusID
WHERE bh.billID=@billID;

when i try to fetch these columns from the required tables

ba.attachmentID ,
        @path + ba.fileName AS asad ,
        ba.attachmentDescription ,
        ba.billAttachmentStatus,

Output is:

166 3   2015-11-30 11:44:18.663 Approve
166 3   2015-11-30 11:44:18.663 Approve
166 5   2015-11-30 11:44:42.600 Paid
166 5   2015-11-30 11:44:42.600 Paid

but desired output is:

166 3   2015-11-30 11:44:18.663 Approve
166 5   2015-11-30 11:44:42.600 Paid

Answer 1

Use DISTINCT:

Specifies that only unique rows can appear in the result set

SELECT DISTINCT bh.billID,bh.statusID,bh.modifiedOn,bs.statusName 
FROM dbo.eInvoice_tbl_billHistory AS bh
--INNER JOIN dbo.eInvoice_tbl_billAttachment AS ba ON bh.billID = ba.billID
--INNER JOIN dbo.eInvoice_tbl_billAttachment AS ba ON ba.billID = bh.billID
INNER JOIN dbo.eInvoice_tbl_billStatus AS bs ON bh.statusID = bs.statusID
WHERE bh.billID=@billID;

Other method is to use GROUP BY:

SELECT bh.billID,bh.statusID,bh.modifiedOn,bs.statusName 
FROM dbo.eInvoice_tbl_billHistory AS bh
--INNER JOIN dbo.eInvoice_tbl_billAttachment AS ba ON bh.billID = ba.billID
--INNER JOIN dbo.eInvoice_tbl_billAttachment AS ba ON ba.billID = bh.billID
INNER JOIN dbo.eInvoice_tbl_billStatus AS bs ON bh.statusID = bs.statusID
WHERE bh.billID=@billID
GROUP BY bh.billID,bh.statusID,bh.modifiedOn,bs.statusName;