Duplicate data with multiple outer joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicate data with multiple outer joins

Author

Topic

arcticpro
Starting Member

6 Posts

Posted - 2011-01-20 : 19:57:22

Hi, I am trying to create a report showing all employees in our company and then include hobbie, skill, and goal info where applicable. So I am joining a total of 4 tables. I get the report to run. If there are two hobbies it creates two rows for the employee as expected, but if there is only one skill or goal, it repeats that data in the second row. How can I have my query omit data when it's duplicate? Here's what I have now:

select p.employeename, ph.hobbie, ps.skill, pg.goal
from employee p
left outer join hobbies ph on p.id = ph.id
left outer join skills ps on p.id = ps.id
left outer join goals pg on p.id = pg.id
order by p.employeename

Gives results like this:

Joe Swimming C++ Python
Joe Hiking C++ Python

I would like to get this:

Joe Swimming C++ Python
Joe Hiking

Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-01-21 : 04:36:18

That's a presentation issue which is best resolved using your front-end presentation tool.
SQL will just return all the data.

arcticpro
Starting Member

6 Posts

Posted - 2011-01-21 : 12:18:46

Unfortunately we don't have a front-end tool. I supply raw SQL reports, so want to be able to remove this duplicate data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources