join two tables on Column where column has value specified more than once

Question

join two tables on Column where column has value specified more than once

hegdesuchi

Hall of Fame

Points: 3047
More actions
November 3, 2016 at 1:22 pm

#331510

I have two tables with below data
table 1
Id name company
1 aaa yyy
1 aaa bbb
table 2
Id designation
1 manager
1 manager
When I join these on ID, I get 4 rows but instead I want to get two rows.
target table:
Id name company designation
1 aaa bbb manager
1 aaa yyy manager
how can I get this ?
In the example company is changed. It can remain same or it can be different.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

hegdesuchi (11/3/2016)
I have two tables with below data
table 1
Id name company
1 aaa yyy
1 aaa bbb
table 2
Id designation
1 manager
1 manager
When I join these on ID, I get 4 rows but instead I want to get two rows.
target table:
Id name company designation
1 aaa bbb manager
1 aaa yyy manager
how can I get this ?
In the example company is changed. It can remain same or it can be different.

1) When posting for help, please provide create table statements in inserts to populate with sample data.

2) Solutions include:

a) deleting duplicate rows from table 2. If you delete one of the manager 1 records you will get the correct output.

b) Adding a company column to table 2 and including that on the join.

c) Using a DISTINCT (or other solution such as ROW_NUMBER() = 1) to remove duplicates from table 2 in a derived table or CTE then joining on that.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 2

hi ,

Thank you!

I used distinct and got the answer.

I have to stage the data from XML file into different tables and then use joins to put them together . once we consolidate everything, we will udpate the original table.

drew.allen SSC Guru Points: 76989 More actions · Answer 3

hegdesuchi (11/3/2016)
hi ,
Thank you!
I used distinct and got the answer.
I have to stage the data from XML file into different tables and then use joins to put them together . once we consolidate everything, we will udpate the original table.

It sounds like it would be much simpler to just pull the information directly from the XML files. Why are you storing them in different tables first?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 4

My XML file has different nodes with different information. I have to put them in different tables and join them on their respective Id's and ParentId's.it is not in same format.

SQLDave2015 SSC Enthusiast Points: 167 More actions · Answer 5

Then you need to create a view that remove the duplicate value that you gong to join. Then join it to the newly create view with your table. Make all the data clean and error free before you join.

________________
IT jobs
IT jobs in Chicago
IT jobs in Dallas

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

In general, I'd say this is a serious problem with the data or the structure, but, you could treat it like versioned data and simply follow one of the approaches listed here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning