SQL: Avoid duplicate data

Question

Table1:

Person_ID   Name    Salary_Revisions
1           Test1   100
1           Test1   200
2           Test2   300
2           Test2   400

Table2:

Person ID                  Department
-------------------------- ---------------- 
1                          Physics
1                          Chemistry
2                          Maths

I would like to get the result like:

Person_ID             Name               Salary_Revisions       Department
--------------------- ------------------ ---------------------- --------------
1                     Test1              100                    Physics
1                     Test1              200                    Chemistry
2                     Test2              300                    Maths
2                     Test2              400

Actual:

Person ID          Name      Salary Revisions      Department
------------------ --------- --------------------- ----------------
1                  Test1     100                   Physics
1                  Test1     200                   Physics
1                  Test1     100                   Chemistry
1                  Test1     200                   Chemistry
2                  Test2     300                   Maths
2                  Test2     400                   Maths

Could you please help me to implement like the expected result?

While implementing this I wrote a stored procedure by left joining Table 1 with Table 2 using person id. By executing the query in Database It returns like Actual result.

SQL Query:

SELECT table1.person_ID, table1.name, table1.salary_revisions, table2.department 
from table1 
left outer join table2 on table1.person_id=table2.person_id

Where does that row with Salary_Revisions = 400 come from?? Doesn't seem to be in the base tables.... you need to explain your logic - it's not clearly obvious from just those bits of data you're showing us here... — marc_s
– marc_s, Commented May 19, 2013 at 11:38
How do you determine that person 1 has 100 salary revisions in the Physics department, and not in the Chemistry department? — Andomar
– Andomar, Commented May 19, 2013 at 11:44
Sorry.. I missed salary_revisions=400. I have corrected the data. — user2395176
– user2395176, Commented May 19, 2013 at 12:07
@user2395176 Still, what would make 100 the correct value for Person1/Physics and not Person1/Chemistry? — Joachim Isaksson
– Joachim Isaksson, Commented May 19, 2013 at 12:12
Hi Joachim Isaksson, I would like to get a representation. I know while joining 100 will join to Physics and Chemistry. I would like to know how can I avoid this duplication. Is it possible? — user2395176
– user2395176, Commented May 19, 2013 at 12:20

Joachim Isaksson · Accepted Answer · 2013-05-19 15:56:26Z

1

I can't quite understand the usage scenario, but I think this is what you want.

Two CTE's to get all unique values for each column per person, and a FULL OUTER JOIN to combine them row by row.

WITH salary_revision AS (
  SELECT person_id, name, salary_revisions sr, 
  ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id) row
  FROM table1
), department AS (
  SELECT person_id, department,
  ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id) row
  FROM table2
)
SELECT sr.person_id,sr.name, sr.sr, d.department
FROM salary_revision sr
FULL OUTER JOIN department d
  ON sr.person_id = d.person_id
 AND sr.row = d.row

An SQLfiddle to test with.

edited May 19, 2013 at 15:56

answered May 19, 2013 at 12:38

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2395176 Over a year ago

Hi Joachim,I would like to get all the departments from table 2, but not duplicated, not the max(department). Thanks

user2395176 Over a year ago

Hi Joachim, I would like to get all the person_ids after join. If my Table 2 contain 3 departments for person_ID 1 then how can I proceed? Please....

Joachim Isaksson Over a year ago

@user2395176 I'm not sure what you're asking...? In what way is the result from the query not giving you your desired result? If you need another result than the one you gave, please add it to the question.

Hogan · Accepted Answer · 2013-05-19 20:19:01Z

Given your comment that there is no relationship between Revisions and Department then it makes sense to use a list. Comma separated looks nice. Like this:

 PERSON_ID  NAME   SALARY REVISION LIST  DEPARTMENT LIST
 ---------- ------ --------------------- ------------------------
 1          Test1  100, 200              Physics, Chemistry
 2          Test2  300, 400              Maths

Here is the query:

SELECT DISTINCT
       Person_ID,
       Name,
       STUFF((SELECT ', ' + CAST(S.Salary_Revisions AS VARCHAR(50))
              FROM Table1 S
              WHERE S.Person_ID = P.Person_ID
              FOR XML PATH ('')),1,2,'') AS [Salary Revision List],
       STUFF((SELECT ', ' + D.Department
              FROM Table2 D
              WHERE D.Person_ID = P.Person_ID
              FOR XML PATH ('')),1,2,'') AS [Department List]
FROM Table1 P

And here is the fiddle: http://sqlfiddle.com/#!3/27c07/3/0

Original Answer

Well this would be easy to do but there is one part of your business rules that don't make sense. Why does this row not have a department?

Person_ID             Name               Salary_Revisions       Department
--------------------- ------------------ ---------------------- --------------
2                     Test2              400

There is nothing about table 2 that implies the difference between user 2 at 300 and user 2 at 400.

What data in the your system implies the results you show are correct -- couldn't the result also be:

Person_ID             Name               Salary_Revisions       Department
--------------------- ------------------ ---------------------- --------------
1                     Test1              100                    Physics
1                     Test1              200                    Chemistry
2                     Test2              300                    
2                     Test2              400                    Maths

or

Person_ID             Name               Salary_Revisions       Department
--------------------- ------------------ ---------------------- --------------
1                     Test1              100                    Physics
1                     Test1              200                    Chemistry
2                     Test2              300                    Maths
2                     Test2              400                    Maths

If you don't have data to logically pick the correct one, there is no way to perform this query.

Maybe the order of the rows matter (which would be very strange for SQL). Maybe there is some data you did not put into your data model which is needed?

I would like to get a representation. I mean like Person_ID 1 have two salary revisions and two departments. Person_ID 2 have two salary revisions and one department. I know here is no way to pick up data logically. But I would like to get a representation. Thanks

user2395176 · Accepted Answer · 2013-05-23 18:23:48Z

    WITH salary_revision AS (
     SELECT person_id, name, salary_revisions sr, 
     ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id) row
     FROM table1
    ), department AS (
     SELECT person_id, department,
     ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id) row
    FROM table2
    )

SELECT COALESCE(sr.person_id,d.person_id),sr.name, sr.sr, d.department FROM salary_revision sr FULL OUTER JOIN department d ON sr.person_id = d.person_id AND sr.row = d.row

be giving COALESCE it consider first non null values. So we get peson_id in each row.

Collectives™ on Stack Overflow

SQL: Avoid duplicate data

3 Answers 3

3 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Related