LEFT OUTER JOIN and NOT EXISTS query

Question

I am having hard time with a complex join, where I need to join the twice the same two tables, and be sure that the join is done every time only with a specific record (the most recent).

SELECT c.collection_id      AS collections, 
       s.strat_id           AS StrategyId, 
       s.strat_version      AS StrategyVersion, 
       ssb.side_ordering    AS SSB_SO, 
       ssb2.side_ordering   AS SSB2_SO, 
       Invoice1.invoice_id  AS Inv1ID, 
       Invoice2.invoice_id  AS Inv2ID, 
       Invoice1.printeddate AS Inv1PrintedDate, 
       Invoice2.printeddate AS Inv2PrintedDate 
  FROM dbo.collections AS c 
       INNER JOIN dbo.strategies AS s 
               ON c.collection_id = s.collection_id 
       INNER JOIN dbo.side_strat_brkrgs AS ssb 
               ON s.collection_id = ssb.collection_id 
                  AND s.strat_id = ssb.strat_id 
                  AND s.strat_version = ssb.strat_version 
       INNER JOIN dbo.strat_sides AS ss 
               ON ss.strat_side_id = ssb.strat_side_id 
       LEFT OUTER JOIN dbo.side_strat_brkrgs AS ssb2 
                    ON ssb2.collection_id = ssb.collection_id 
                       AND ssb2.strat_id = ssb.strat_id 
                       AND ssb2.strat_version = ssb.strat_version 
                       AND ssb2.side_ordering <> ssb.side_ordering 
       INNER JOIN dbo.strat_sides AS ss2 
               ON ss2.strat_side_id = ssb2.strat_side_id 
       LEFT OUTER JOIN dbo.newinvoiceitem AS InvoiceItem1 
                    ON ssb.collection_id = InvoiceItem1.collection_id 
                       AND ssb.side_ordering = InvoiceItem1.side_ordering 
                       AND s.strat_id = InvoiceItem1.strat_id 
       LEFT OUTER JOIN dbo.newinvoice AS Invoice1 
                    ON Invoice1.invoice_id = InvoiceItem1.invoice_id 
       LEFT OUTER JOIN dbo.newinvoiceitem AS InvoiceItem2 
                    ON ssb2.collection_id = InvoiceItem2.collection_id 
                       AND ssb2.side_ordering = InvoiceItem2.side_ordering 
                       AND s.strat_id = InvoiceItem2.strat_id 
       LEFT OUTER JOIN dbo.newinvoice AS Invoice2 
                    ON Invoice2.invoice_id = InvoiceItem2.invoice_id 
 WHERE NOT EXISTS (SELECT 1 
                     FROM dbo.newinvoiceitem tempInvoiceItem1 
                          INNER JOIN dbo.newinvoice AS tempInvoice1 
                                  ON tempInvoice1.invoice_id = 
                                     tempInvoiceItem1.invoice_id 
                    WHERE tempInvoiceItem1.collection_id = ssb.collection_id 
                          AND ssb.side_ordering = tempInvoiceItem1.side_ordering 
                          AND s.strat_id = tempInvoiceItem1.strat_id 
                          AND s.strat_version = tempInvoiceItem1.strat_version 
                          AND tempInvoice1.printeddate > Invoice1.printeddate) 
       AND NOT EXISTS (SELECT 1 
                         FROM dbo.newinvoiceitem tempInvoiceItem2 
                              INNER JOIN dbo.newinvoice AS tempInvoice2 
                                      ON tempInvoice2.invoice_id = 
                                         tempInvoiceItem2.invoice_id 
                        WHERE 
               tempInvoiceItem2.collection_id = ssb2.collection_id 
               AND ssb2.side_ordering = tempInvoiceItem2.side_ordering 
               AND s.strat_id = tempInvoiceItem2.strat_id 
               AND s.strat_version = tempInvoiceItem2.strat_version 
               AND tempInvoice2.printeddate > Invoice2.printeddate) 
       AND c.collection_id = 16447 

I obtain the following result:

16447   1   3   1   0   3785    3183    2010-05-06 17:52:00 2010-05-06 17:52:00 
16447   1   3   1   0   3785    4033    2010-05-06 17:52:00 2010-05-10 16:32:00 
16447   1   3   1   0   4137    3183    2010-05-20 17:08:00 2010-05-06 17:52:00 
16447   1   3   1   0   4137    4033    2010-05-20 17:08:00 2010-05-10 16:32:00

While I am actually expecting only the last row. Where am I wrong?

As you see I can't be using MAX() to retrieve the highest record, because I need to look for another property through a JOIN, and I am forced to use a NOT EXIST

Answer 1

Try this predicate

 WHERE EXISTS (SELECT 1 
               FROM dbo.newinvoiceitem tempInvoiceItem1 
                 INNER JOIN dbo.newinvoice AS tempInvoice1 
                   ON tempInvoice1.invoice_id = tempInvoiceItem1.invoice_id 
               WHERE tempInvoiceItem1.collection_id = ssb.collection_id 
                 AND ssb.side_ordering = tempInvoiceItem1.side_ordering 
                 AND s.strat_id = tempInvoiceItem1.strat_id 
               HAVING MAX(tempInvoice1.printeddate) = Invoice1.printeddate
                 OR Invoice1.printeddate IS NULL
               )                          
       AND EXISTS (SELECT 1 
                   FROM dbo.newinvoiceitem tempInvoiceItem2 
                     INNER JOIN dbo.newinvoice AS tempInvoice2 
                       ON tempInvoice2.invoice_id = tempInvoiceItem2.invoice_id 
                   WHERE tempInvoiceItem2.collection_id = ssb2.collection_id 
                     AND ssb2.side_ordering = tempInvoiceItem2.side_ordering 
                     AND s.strat_id = tempInvoiceItem2.strat_id 
                   HAVING MAX(tempInvoice2.printeddate) = Invoice2.printeddate
                     OR Invoice2.printeddate IS NULL
                   ) 
       AND c.collection_id = 16447 

OR

 WHERE EXISTS (SELECT 1 
               FROM dbo.newinvoiceitem tempInvoiceItem1 
                 INNER JOIN dbo.newinvoice AS tempInvoice1 
                   ON tempInvoice1.invoice_id = tempInvoiceItem1.invoice_id 
               WHERE tempInvoiceItem1.collection_id = ssb.collection_id 
                 AND ssb.side_ordering = tempInvoiceItem1.side_ordering 
                 AND s.strat_id = tempInvoiceItem1.strat_id 
               HAVING MAX(tempInvoice1.printeddate) = Invoice1.printeddate                     
               )                          
       AND EXISTS (SELECT 1 
                   FROM dbo.newinvoiceitem tempInvoiceItem2 
                     INNER JOIN dbo.newinvoice AS tempInvoice2 
                       ON tempInvoice2.invoice_id = tempInvoiceItem2.invoice_id 
                   WHERE tempInvoiceItem2.collection_id = ssb2.collection_id 
                     AND ssb2.side_ordering = tempInvoiceItem2.side_ordering 
                     AND s.strat_id = tempInvoiceItem2.strat_id 
                   HAVING MAX(tempInvoice2.printeddate) = Invoice2.printeddate
                   ) 
       AND c.collection_id = 16447 OR Invoice1.printeddate IS NULL
         OR Invoice2.printeddate IS NULL