CODEWITHSUNDEEP
sql
Sixthsense
Sixthsense

Reputation: 1975

Inner Join Creates Duplicate Records

Why does this inner join create duplicates?

SELECT 
    AU.sProdCode, AU.sProdName
FROM 
    AllowedUsers AU
INNER JOIN 
    ProdMaster PM ON AU.sProdCode = PM.sProdCode
WHERE
    PM.sProdStatus = 'pending' AND
    AU.sUserCode = 'TM001'

How to get rid of the duplicate issue?

ProdMaster:

sProdCode   sProdName   sProdStatus
-----------------------------------
1001        MyProduct   Pending

AllowedUsers:

sProdCode   sProdName   sUserCode
----------------------------------
1001        MyProduct   TM001

My condition

  1. If the Product status is Pending (In ProdMaster)
  2. User is allowed to view the product (In Allowed User - User code)
  3. Show the product code / Product name without duplicate.

Upvotes: 2

Views: 13817

Answers (3)

Daniel Ortiz Rosaslanda
Daniel Ortiz Rosaslanda

Reputation: 1

In my case, I developed a table to simulate custom sort order ( when A-Z, 1-9 is not an option ) when I tried to join 2 elements I got two rows of result, until I grouped inside query then I was able to order

Here is the code sample:

select x.thg, 
       x.Business_Capability as BC, 
       x.TotApps
from (
        select [inv].[thg], [inv].[Business_Capability], count(1) as TotApps, 
        **thgO.Srtorder as thgo, bcpO.Srtorder as bcpo**

         FROM [dbo].[cbVcsi] as inv
         inner join **cbSOcat thgO** on   ( thgO.Family = 'thgrp' and thgO.Item = inv.thg )
         inner join **cbSOcat bcpO** on   ( bcpO.Family = 'BizCap' and bcpO.Item = inv.Business_Capability )
         where  [inv].[Business_Capability] not in (  'Automation', 'Testing' ) and [inv].[ParentAppId] = 0
         Group By [inv].[thg], [inv].[Business_Capability], **thgO.Srtorder, bcpO.Srtorder**
) AS x
order by  **x.thgo, x.bcpo**

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Apparently, you have duplicate rows, presumably in the second table.

The most efficient approach would be exists:

SELECT AU.sProdCode, AU.sProdName
FROM AllowedUsers AU
WHERE EXISTS (SELECT 1
              FROM ProdMaster PM 
              WHERE AU.sProdCode = PM.sProdCode AND PM.sProdStatus = 'pending'
             )
WHERE AU.sUserCode = 'TM001';

For best performance, you want indexes on ProdMaster(sProdCode, sProdStatus), and AllowedUsers(sUserCode, sProdName).

If you are still getting duplicates, then you have duplicated in AllowedUsers. In that case, use SELECT DISTINCT.

Upvotes: 3

prashant sugara
prashant sugara

Reputation: 321

Just use the distinct as below:

SELECT distinct AU.sProdCode,AU.sProdName
FROM AllowedUsers AU
inner JOIN ProdMaster PM
ON AU.sProdCode = PM.sProdCode
where PM.sProdStatus = 'pending' and
AU.sUserCode = 'TM001'

And the reason is you would have 1 to many relationship in tables.

Upvotes: 1

Related Questions