Oracle SQL – Avoid Selecting Duplicate Records from Table

SELECT
  *
FROM 
  foos 
  INNER JOIN 
  (
    SELECT
      name
    FROM 
      foos 
    GROUP BY 
      name 
    HAVING 
      COUNT(*) > 1 
  ) AS dup
  ON foos.name = dup.name
ORDER BY 
  name

The query that returns 609 rows apparently has only 12 distinct parentID values. If you use it as an IN subquery matched against a unique column (which I am assuming ffPackage.ID is), you can only get 12 rows as the result.

If you want to get as many rows as the subquery returns, one way is to use the subquery as a derived table and join it to ffPackage:

SELECT
  serialnumber
FROM
  dbo.ffPackage AS p
  INNER JOIN
  (
    -- the 609 row subquery
    select parentID from ffPackage where SerialNumber in (
    select ffPackage.SerialNumber from ffEmployee, ffHistory, ffSerialNumber, ffPackage, ffUnitDetail
    where ffHistory.UnitID in (
    select UnitID from ffUnitDetail where OutmostPackageID in (
    select ID from ffPackage where SerialNumber = 'xxxxxxxx'))
    and ffHistory.EmployeeID = ffEmployee.ID
    and ffHistory.UnitID = ffSerialNumber.UnitID
    and ffHistory.UnitStateID = 'xxxx'
    and ffUnitDetail.UnitID = ffHistory.UnitID
    and ffUnitDetail.InmostPackageID = ffPackage.ID
    ))
  ) AS sub ON p.ID = sub.ParentID
;

Incidentally, I would like to suggest that you rewrite the comma joins in the subquery as explicit joins – for consistency and maintainability's sake:

SELECT
  SerialNumber
FROM
  dbo.ffPackage AS p
  INNER JOIN
  (
    -- the 609 row subquery
    SELECT
      ParentID
    FROM
      dbo.ffPackage
    WHERE
      SerialNumber IN
      (
        SELECT
          p.SerialNumber
        FROM
          dbo.ffEmployee AS e
          INNER JOIN dbo.ffHistory AS h ON h.EmployeeID = e.ID
          INNER JOIN dbo.ffSerialNumber AS sn ON h.UnitID = sn.UnitID
          INNER JOIN dbo.ffUnitDetail AS ud ON ud.UnitID = h.UnitID
          INNER JOIN dbo.ffPackage AS p ON ud.InmostPackageID = p.ID
        WHERE
          h.UnitID IN
          (
            SELECT
              UnitID
            FROM
              ffUnitDetail
            WHERE
              OutmostPackageID IN
              (
                SELECT ID FROM ffPackage WHERE SerialNumber = 'xxxxxxxx'
              )
          )
          AND h.UnitStateID = 'xxxx'
      )
  ) AS sub ON p.ID = sub.ParentID
;

I would also argue that using better formatting and short (but meaningful) table aliases contributes to maintainability of your queries too. And you may want to consider getting into the habit of always qualifying your tables with the schema name as well as that of using statement terminators.