SQL Query - Minus

SELECT 
	[pro number],
	[net amount due],
	[amount paid],
	convert(datetime,[hold reason 4])
FROM 
	[processing table]
WHERE 
	[process week] BETWEEN '8/1/2012' AND '8/31/2012'
MINUS
SELECT     
	*
FROM         
	[GE8-31a]
WHERE     
	Pro IN (SELECT     
			[Pro Number]
                FROM          
			[Processing Table]
                WHERE      
			[Process Week] BETWEEN '8/1/2012' AND '8/31/2012')

Open in new window

Good Day Experts!

I am in a bit of a quandry here.  I have one query that returns 46,419 records and a second one that returns 49,004 records.  I have the resulting data in seperate worksheets in Excel.  Now I want to make a third worksheet to hold the 2,585 records that are in the second query not in the first one.  So, I thought of the minus operator in SQL.  Only problem is I get an error in SQL Query Analyzer when I try to do the minus.  Erorr indicates--> Incorrect syntax near 'MINUS'.

I have never used minus before.  Perhaps I have something wrong.  Can you help?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
EXCEPT is supported since MSSQL 2005 - you using Query Analyzer hints that you are working with MSSQL 2000, correct? In that case there is no other way than to use a NOT EXISTS, or transform the whole statement to an optimized one:
SELECT 
  [pro number],
  [net amount due],
  [amount paid],
  convert(datetime,[hold reason 4])
FROM 
  [processing table] pt
WHERE 
  [process week] BETWEEN '8/1/2012' AND '8/31/2012'
and [Pro NUmber] not in (select Pro FROM   [GE8-31a])

Open in new window

0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
you should be using EXCEPT instead

SELECT
      [pro number],
      [net amount due],
      [amount paid],
      convert(datetime,[hold reason 4])
FROM
      [processing table]
WHERE
      [process week] BETWEEN '8/1/2012' AND '8/31/2012'
EXCEPT
SELECT    
      *
FROM        
      [GE8-31a]
WHERE    
      Pro IN (SELECT    
                  [Pro Number]
                FROM          
                  [Processing Table]
                WHERE      
                  [Process Week] BETWEEN '8/1/2012' AND '8/31/2012')
0
 
Jimbo99999Author Commented:
Good Day

I have tried and receive -->Incorrect syntax near the keyword 'Except'
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Aneesh RetnakaranDatabase AdministratorCommented:
except is available from SQL Server 2005 onwards, for SQL 2000 you should use either a NOT IN clause or NOT EXIXTS clause.
0
 
Jimbo99999Author Commented:
Ok...yes 2000.  I have tried your suggestion but I am perplexed wit hthe results.
I have 2793 rows but when I run the queries seperately I get a difference of 2585 records.
0
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
The NOT IN does not check for full records, only for same PRO/PRO NUMBER. If that isn't unique, the result is different. If you want a full check, we need to go the NOT EXISTS way, and compare all important columns:
SELECT 
  [pro number],
  [net amount due],
  [amount paid],
  convert(datetime,[hold reason 4])
FROM 
  [processing table] pt
WHERE 
  [process week] BETWEEN '8/1/2012' AND '8/31/2012'
and not exists (
  select * from [GE8-31a] ge
  where ge.PRO              = PT.[pro number]
    and ge.[Net amount due] = PT.[Net amount due]
    and ge.[amount paid]    = PT.[amount paid]
    and ge.[hold reason 4]  = PT.[hold reason 4]
)

Open in new window

I had to guess the [GE8-31a] column names.
0
 
sfmnyConnect With a Mentor Commented:
Hello there,

When you use MINUS or UNION operators, the list of columns you select have to be the same. Change the "SELECT *" to match the columns in the first "SELECT ..."

These operators are similar to UNIONS and MINUS in set theory and the elements need to be the same to carry out that operation.
0
 
deightonConnect With a Mentor progCommented:
LEFT Join on all fileds on left able, then select rows that are null on primary key of right table
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.