none
Get headers where there are no associated details/lines

    Question

  • I have a SalesOrderHeader Table and a SalesOrderDetail Table.

    They are linked by a SalesOrderID.  Each SalesOrderHeader may have multiple SalesOrderDetails.

    I want to find all SalesOrderHeaders that do NOT have any SalesOrderDetails lines.

    Can I do a join where the SalesOrderID's are not equal?

    Any Suggestions?

    Tuesday, October 29, 2013 9:32 PM

Answers

All replies

  • SELECT soh.SalesOrderID
    FROM   SalesOrderHeader soh
           LEFT JOIN (SELECT DISTINCT SalesOrderID
                      FROM   SalesOrderDetails) t
                  ON soh.SalesOrderID = t.SalesOrderID
    WHERE  t.SalesOrderID IS NULL 

    Tuesday, October 29, 2013 9:45 PM
    Moderator
  • USE AdventureWorks2012
    GO
    
    SELECT  *
    FROM    Sales.SalesOrderHeader OH
            LEFT OUTER JOIN Sales.SalesOrderDetail OD ON OH.SalesOrderID = OD.SalesOrderID
    WHERE   OD.SalesOrderID IS NULL
    
    
    SELECT  *
    FROM    Sales.SalesOrderHeader OH
    WHERE   NOT EXISTS ( SELECT *
                         FROM   Sales.SalesOrderDetail OD
                         WHERE  OH.SalesOrderID = OD.SalesOrderID )
    


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Tuesday, October 29, 2013 9:47 PM
  • Saeid, wait for me :-( you are answering to fast... it's taking me time to write in English, and i till i write (i check before i post) i see that you already answered.

    I'm kidding of course
    But you know there is a saying, that "there is some real in any joke" :-)


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, October 29, 2013 10:10 PM