WHERE NOT EXISTS missing right parenthesis problem.

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?
gdlp2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
where would be the interest in having an order by in a WHERE NOT EXISTS () ?

remove the order by clause from there.
0
gdlp2004Author Commented:
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
sachinwadhwaCommented:
try "NOT IN" clause with joining 2 fields
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

calycifloraeCommented:
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
calycifloraeCommented:
I mean give NOT IN in followed byt (SELECT *.......)
WHERE conditions.

hope it helps
0
Arthur_WoodCommented:
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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

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.