CODEWITHSUNDEEP
sql-servert-sqlduplicates
Ryden Choi
Ryden Choi

Reputation: 307

Why are query results duplicated? SQL Server

I'm trying to write a query. I want to return only one rows, but my query returns multiple duplicated rows.

My query is below

SELECT
    LOWER(M.UserID) AS EmpID,
    ISNULL(M.DispName, '') AS UserName,
    ISNULL(C.CompCode, '') AS CompCode,
    ISNULL(D.DispName, '') AS DeptName,
    ISNULL(D.DispName, '') AS DispName,
    ISNULL(R.RankCode, '') AS RankCode,
    M.SortOrder AS UserOrder,
    R.SortOrder AS RankOrder,
    ISNULL(J.JobCode, '') AS JobCode
FROM 
    tb_User M WITH(NOLOCK)
INNER JOIN 
    tb_Dept D WITH(NOLOCK) ON M.MainDeptCode = D.DeptCode
INNER JOIN 
    tb_Company C WITH(NOLOCK) ON M.CompCode = C.CompCode
INNER JOIN 
    tb_Rank R WITH(NOLOCK) ON M.RankCode = R.RankCode
INNER JOIN 
    tb_Duty DT WITH (NOLOCK) ON M.DutyCode = DT.DutyCode
LEFT OUTER JOIN 
    tb_Job J WITH (NOLOCK) ON M.JobCode = J.JobCode 
WHERE 
    MainDeptCode = '1000' AND M.DisplayYN = 'Y'
ORDER BY
    RankOrder, JobCode, DispName

And this is the result:

EmpID UserName CompCode DeptName DispName RankCode UserOrder RankOrder JobCode 
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0
0001  ryden    1000     ceo       ceo      1         0          0

How can I fix that query?? Please help me.

Upvotes: 0

Views: 197

Answers (2)

kosmaskaf
kosmaskaf

Reputation: 28

If you have multiple rows in your output, the problem is that one of your tables has multiple rows, or you did not join it correctly. I would call select * to see which table this is. In your case I believe it is the table tb_Duty because you are not using it.

Upvotes: 1

pete the pagan-gerbil
pete the pagan-gerbil

Reputation: 3166

The 'quick and dirty' way is to add DISTINCT to the SELECT query - 

SELECT DISTINCT * FROM....

But that doesn't actually fix the problem.

The proper way is to work out why. Without knowing the data in those tables, it's not going to be possible to tell. The most common cause is that one of the tables you've joined has multiple rows that match the join criteria, but they differ in the columns that are not included in the SELECT (so they don't appear to be any different).

To work through this, comment out all of your joins (and their associated columns in SELECT) and add them in one by one, to see which table is adding multiple rows. Then you can select all columns from that table to see exactly which rows are being matched, and why, based on the criteria you've chosen to join on.

Once you've identified the rows, you'll need to work out a new set of JOIN or WHERE clauses to exclude all the additional rows that you don't want.

This should help you in the majority of cases, but there's a chance with some schemas that you'll have to do something more involved (subqueries, common table expressions, etc).

Upvotes: 2

Related Questions