EXISTS -- NOT EXISTS QUERY

Good Morning
CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
QUERY BELOW WITH A SINGLE QUERY LIKE THIS
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOTEXISTS(select table2.fieldb from mytable where
table2.fieldb=mytable.A)
INSTEAD OF
SELECT mytable.A from mytable
where
exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
UNION
SELECT mytable.A from mytable
where
NOTEXISTS(select table2.fieldB from mytable where
table2.fieldB=mytable.A)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!On Thu, 12 Aug 2004 06:12:38 -0700, David Hills wrote:

>Good Morning
>CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
>QUERY BELOW WITH A SINGLE QUERY LIKE THIS
>SELECT mytable.A from mytable
>where
>exists(select table2.fielda from table2 where table2.fielda=mytable.A)
>OR
>NOTEXISTS(select table2.fieldb from mytable where
>table2.fieldb=mytable.A)
>
>INSTEAD OF
>SELECT mytable.A from mytable
>where
>exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
>UNION
>SELECT mytable.A from mytable
>where
>NOTEXISTS(select table2.fieldB from mytable where
>table2.fieldB=mytable.A)
>
>
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
Hi David,
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOT EXISTS(select table2.fieldb from table2 where
table2.fieldb=mytable.A)
(Note - I copied your query, inserted a space between NOT and EXISTS and
changed the table name in the second subquery from mytable to table2;
that's all that was needed. I'm surprised your original version with UNION
worked, as this one contains the same errors).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Does the following do what you want?
SELECT mytable.A
FROM mytable
WHERE EXISTS
(
SELECT *
FROM table2
WHERE table2.fielda=mytable.A
)
OR
NOT EXISTS
(
SELECT *
FROM table2
WHERE table2.fieldb=mytable.A
)
Hope this helps.
Dan Guzman
SQL Server MVP
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23S8fL4GgEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Good Morning
> CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
> QUERY BELOW WITH A SINGLE QUERY LIKE THIS
> SELECT mytable.A from mytable
> where
> exists(select table2.fielda from table2 where table2.fielda=mytable.A)
> OR
> NOTEXISTS(select table2.fieldb from mytable where
> table2.fieldb=mytable.A)
>
> INSTEAD OF
> SELECT mytable.A from mytable
> where
> exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
> UNION
> SELECT mytable.A from mytable
> where
> NOTEXISTS(select table2.fieldB from mytable where
> table2.fieldB=mytable.A)
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||No I'm affraid it does not work.
I am running against oracle and the query stalls.
The union works and takes about 5 seconds
thanks
dave
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> No I'm affraid it does not work.
> I am running against oracle and the query stalls.
> The union works and takes about 5 seconds
Then perhaps you should upgrade to SQL Server :-)
Oracle questions are best asked in an Oracle forum. This one is specific to
Micrososft SQL Server. The only suggestion I can make is that you make sure
you have indexes on the columns references in your WHERE clauses.
Hope this helps.
Dan Guzman
SQL Server MVP