help with an inner join of parent child

• ldejesuspr

  Mr or Mrs. 500

  Points: 551

  More actions

  July 23, 2004 at 8:22 pm

  #112095

  I have three tables that i want to join: employees, WorkedHours and Dependants.

  the result i want is the info. from employees with the hours worked and the name of the wife that is in the dependant table, this table also include children.

  Since the table of WorkedHours and Dependants have many records for the same employee I get duplicated records.  For instance if the emp. have 2 entries in the WorkedHours table and 4 entries in Dependants i want the returning recordset to be of 2 rows, each for the hours and the name of the wife in each not the name of the children. Instead i get 4 rows. The condition in Dependants is relationship='spouse'

  thanks for the help

  Leslie

   

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 24, 2004 at 3:48 pm

  #515982

  Hey There Leslie,

  Check out if this SQL is what you are looking for:

  SELECT

    *

  FROM

    WorkedHours w INNER JOIN

      Employees e LEFT OUTER JOIN

        Dependants d ON e.employeeID = d.employeeID AND d.relationship='spouse'

    ON

      w.employeeID = e.employeeID

  I do a LEFT OUTER JOIN between Employees and Dependants since there could be cases in which an employee does not have an spouse.

  JP

• ldejesuspr

  Mr or Mrs. 500

  Points: 551

  More actions

  July 24, 2004 at 4:49 pm

  #515983

  thanks JP, it worked perfectly!