none
Duplicate data with multiple outer joins

    Question

  • 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 skill or goal, it repeats that data there.  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.

    Friday, January 21, 2011 12:22 AM

Answers

  • I see that you have repeated the employee name in your desired results.  Is that really what you want?

    Suppressing repeated values is a task better suited for the presentation layer (e.g. report).  This also implies ordering/grouping because the repeated values need to be together before redundant values can be supressed. 

    Consider the raw dataset below of a single employee with 2 skiis and 2 goals.  As you can see, if you supress hobby when it is the same as the previous row, you'll still end up with duplicate skills and goals because there is no correlation between these items besites employee.

    employeename hobbie skill goal
    Joe Hiking C++ C#
    Joe Hiking C++ Python
    Joe Hiking VB C#
    Joe Hiking VB Python
    Joe Swimming C++ C#
    Joe Swimming C++ Python
    Joe Swimming VB C#
    Joe Swimming VB Python

    I suggest you post this question to the Reporting Services forum, assuming that is the reporting technology you are using.  I think there are many creative ways to avoid the duplication and provide a user-friendly report.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, January 21, 2011 2:00 AM

All replies

  • I see that you have repeated the employee name in your desired results.  Is that really what you want?

    Suppressing repeated values is a task better suited for the presentation layer (e.g. report).  This also implies ordering/grouping because the repeated values need to be together before redundant values can be supressed. 

    Consider the raw dataset below of a single employee with 2 skiis and 2 goals.  As you can see, if you supress hobby when it is the same as the previous row, you'll still end up with duplicate skills and goals because there is no correlation between these items besites employee.

    employeename hobbie skill goal
    Joe Hiking C++ C#
    Joe Hiking C++ Python
    Joe Hiking VB C#
    Joe Hiking VB Python
    Joe Swimming C++ C#
    Joe Swimming C++ Python
    Joe Swimming VB C#
    Joe Swimming VB Python

    I suggest you post this question to the Reporting Services forum, assuming that is the reporting technology you are using.  I think there are many creative ways to avoid the duplication and provide a user-friendly report.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, January 21, 2011 2:00 AM
  • The following page has examples for surpressing repeating values in query results:

    http://www.sqlusa.com/bestpractices2005/hiderepeatingcells/

     


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM
    Friday, January 28, 2011 11:29 AM