SQL question on duplicate records - HELP! - THANKS!!

Question

question

Jeff 1 asked Mar 14, '10

SQL question on duplicate records - HELP! - THANKS!!

Up against a SQL question that is difficult to code. Any help is greatly appreciated.

General example. I want to select duplicate records that have the same INVOICE NUMBER with one or more occurences with an INVOICE AMOUNT = $10.00.

DATABASE = TEST                    
TABLE = INVOICES                    
                    
INVOICE NUMBER     INVOICE AMOUNT                    
123                 $20.00                    
234                 $50.00                    
123                 $10.00                    
657                 $11.00                    
123                 $40.00                    
234                 $20.00

I can select duplicates by INVOICE NUMBER using the following:

SELECT [INVOICE NUMBER],[INVOICE AMOUNT]                     
FROM [TEST].[dbo].[INVOICES]                    
WHERE ((([INVOICES].[INVOICE NUMBER]) In                     
     (SELECT [INVOICE NUMBER]                     
      FROM [test].[dbo].[INVOICES] AS Tmp                     
      GROUP BY [INVOICE NUMBER]                     
      HAVING Count(*)>1)));

Resulting set is:

123                 $20.00                    
234                 $50.00                    
123                 $10.00                    
123                 $40.00                    
234                 $20.00

But, I only want to select those duplicates that have one or more occurences where the INVOICE AMOUNT = $10.00.

Desired set would be:

123                 $20.00                    
123                 $10.00                    
123                 $40.00

Can you help??? - THANKS!!!!!

delete-duplicates

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-14T19:38:41Z

Scot Hauder answered Mar 14, '10

SELECT [INVOICE NUMBER]            
      ,[INVOICE AMOUNT]             
FROM [INVOICES]             
WHERE [INVOICE NUMBER] IN (SELECT i1.[INVOICE NUMBER]            
                           FROM [INVOICES] AS i1            
                           JOIN [INVOICES] i2 ON (i1.[INVOICE NUMBER] = i2.[INVOICE NUMBER])            
                           WHERE i1.[INVOICE AMOUNT] = 10.00            
                           GROUP BY i1.[INVOICE NUMBER]             
                           HAVING COUNT(*) > 1);

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-16T16:33:00Z

Brian Perrin answered Mar 16, '10

the self-join is elegant, but perhaps a little overkill - try:

SELECT [INVOICE NUMBER] ,[INVOICE AMOUNT] FROM [INVOICES] i WHERE EXISTS (select 1 from [INVOICES] t where t.[INVOICE NUMBER] = i.[INVOICE NUMBER] and t.[INVOICE AMOUNT] = 10)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question