• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1734
  • Last Modified:

opposite of UNION ?

I understand how the UNION keyword works, in that it enables one to COMBINE queries into one result set ADDITIVELY.

Is there a way to specify a query, and then TAKE AWAY the results of another query..

Eg.

"give me all schools in the extended programme"

"and then remove all that don't have an annual return done"

(the above is not easily done in one query)

We are using SQL Server 2000

many thanks!!
0
zorba111
Asked:
zorba111
1 Solution
 
cyberkiwiCommented:
INTERSECT
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with sql 2000, you either use * WHERE NOT EXISTS()* WHERE NOT IN ()* LEFT JOIN ... WHERE  ... IS NULL or a temp table, and do a DELETE based on the second query ...many ways to shave the cat ..
0
 
cyberkiwiCommented:
Sorry, I didn't read properly, it is EXCEPT. But that only works 2005+

The next best would be NOT IN (subquery) if you have a single column to test on, or LEFT JOIN together with WHERE T2.idcol is null to filter out the matches from the 2nd table.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
TechTiger007Commented:
You can use "minus" to achieve this
eg.
(select * from test) minus (select * from test where result="pass")
0
 
TechTiger007Commented:
Sorry I didnt see that this is about sql server. minus is for oracle
0
 
SrinathKomirishettyCommented:
Try this

--If Second table contains only Annual schools info.
SELECT * FROM FirstTable
WHERE SchoolIdentifierColumn IN (SELECT SchoolIdentifierColumn FROM SecondTable)

--If Second table contains Annual schools info and other than annaul schools info
SELECT * FROM FirstTable
WHERE SchoolIdentifierColumn NOT IN (SELECT SchoolIdentifierColumn FROM SecondTable WHERE AnnualColumn=0)
0
 
zorba111Author Commented:
@angell

I did it using
* LEFT JOIN ... WHERE  ... IS NULL
in combination with
* WHERE NOT EXISTS()
and
* WHERE EXISTS()

I also did it with

or a temp table, and do a DELETE based on the second query ...

so as I could check the result set agains the 2nd method.

both tallied (after a bit of tweaking) !
thank you !!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now