4

I have two tables : Part, OrderLine:

Part (PartNum PK, PartDesc, Price)

OrderLine (OrderNum PK, PartNum PK, QtyOrdered)

And I want to find out the parts that were not ordered. I think I have a good idea, but I may be missing something; I think most likely on the syntax. Please check:

select Part.PartNum
    ,OrderLine.OrderNum
from OrderLine
inner join Part on OrderLine.PartNum = Part.PartNum
where PartNum not in (
        select distinct (OrderNum)
        from OrderLine
        )

Is this correct?

0

1 Answer 1

5

If you want the parts that were not ordered, then you should start with Part, not OrderLine. When you're thinking about sets, that is the only one that could conceivably have the items you're looking for. Part can be a Superset of ordered parts and OrderLine could easily be a Subset of parts.

SELECT PartName
FROM Part
WHERE PartNum NOT IN (
        SELECT PartNum
        FROM OrderLine
        )

More often than not, comparing integers will be far more efficient than strings, so I've changed the WHERE clause to look for PartNums, not PartNames.

4
  • 3
    Also if you are doing a NOT IN then you don't need a distinct on the subquery. Dec 18, 2015 at 20:55
  • @Dave: Don't I have to join with OrderLine first, to be able to refer to it in the inner query?
    – MSIS
    Dec 18, 2015 at 21:56
  • No, the subquery is executed independently of the outer query. See here for more info: databasejournal.com/features/mssql/article.php/3464481/…
    – Dave
    Dec 18, 2015 at 22:20
  • 2
    PartNum is the PK in Part, so the first DISTINCT is not needed either. The query can also be written (if you want only the PartNum in the result and not any other columns from Part) as: SELECT PartNum FROM Part EXCEPT SELECT PartNum FROM OrderLine ; Dec 19, 2015 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.