[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# WHERE NOT EXISTS missing right parenthesis problem.

Posted on 2006-03-23
Medium Priority
529 Views
I am having trouble getting this query to work.  I can run both queries seperately just fine, but when I try to run them together in a WHERE NOT EXISTS clause, I keep getting the dreaded missing right parenthesis error.

Here is the query:

SELECT *
FROM Nine99 n, bookingoptselect bos
WHERE NOT EXISTS
(
SELECT *
FROM Nine99, BookingOptSelect bos
WHERE nine99txt != n.nine99Txt
AND bos.id = bookingOptSelectId
AND bookingid = 19633
AND bookingRevNum = 0
ORDER BY bosUdcName
)
AND bos.id = bookingOptSelectId
AND bookingid = 19633
AND bookingRevNum = 1
ORDER BY bosUdcName

Can you not do order by inside the WHERE NOT EXISTS?
0
Question by:gdlp2004

LVL 143

Expert Comment

ID: 16270255
where would be the interest in having an order by in a WHERE NOT EXISTS () ?

remove the order by clause from there.
0

Author Comment

ID: 16271008
angelIII,

the reason I have that order by clause is to check for each particular number not necessarily to see if that description was not in the entire table.

For example.

BEFORE:             AFTER:
1. apple       --    1. apple
2. grapes     --    2. grapes
3. orange     --    3. grapefruit
4. cherry      --    4. cherry
5. tomato     --    5. bananna

I want the results of the query to be.
1. grapefruit
5. bananna

I was worried that it might compare

apple to grapes and return grapes.  On down.
0

LVL 7

Expert Comment

ID: 16271211
try "NOT IN" clause with joining 2 fields
0

Expert Comment

ID: 16272502
it must work as this...

instead of WHERE NOT EXISTS, give NOT IN
followed by WHERE AND GIVE YOUR SPECIFICATIONS..

I have tried similar problems.
goodluck
0

Expert Comment

ID: 16272527
I mean give NOT IN in followed byt (SELECT *.......)
WHERE conditions.

hope it helps
0

LVL 44

Expert Comment

ID: 16272861
Not Exists will be TRUE is the Select statement that you are testing returns 0 rows, and False it it returns ANY number of rows.  It has nothing at all to do with what fields the select statement returns - just that A record, ANY record is or  is not  returned.

Not IN will allow you to test a specific value  ( the syntax is Where SourceTable.Field NOT IN (Select TestTable.Field from TestTable where TestTable.Condition)

Notice here that the inner select returns only 1 Field (but possibly many rows) - Here it is VERY important what field the inner SQL returns, since it MUST be the same KIND of thing as the field you are testing with (SourceTable.Field and TestTable.Field MUST be the same kind of values).

AW
0

LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 16272872
As angelIII has pointed out you cannot have an ORDER BY clause in a EXISTS; it simply does not make any sense.  If you do you will get the following error message:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Do it this way:
SELECT *
FROM Nine99 n, bookingoptselect bos
WHERE NOT EXISTS
(
SELECT 1
FROM Nine99, BookingOptSelect bos
WHERE nine99txt != n.nine99Txt
AND bos.id = bookingOptSelectId
AND bookingid = 19633
AND bookingRevNum = 0)
AND bos.id = bookingOptSelectId
AND bookingid = 19633
AND bookingRevNum = 1
ORDER BY bosUdcName
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month20 days, 14 hours left to enroll