Sql-server – select in (select) allow to get duplicate values

duplicationselectsql server

I need help in one SQL query.

I have select in select:

select column1 from table where column2 in (
--there I have select command what returns me 609 rows with duplicate values
)

and I need to get in first select 609 rows with duplicates. But I only get 12 rows because the select in command ignores duplicate values in second select.

I need to get certain values (609rows) not only to find what values I get two or more times.
I will join this result to another result.

My query looks like this :

This is the first select I was talking about

select  serialnumber from ffPackage where ID in (

and this is the select from what I get 609 rows. 

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
))

Best Answer

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.

Related Question