CODEWITHSUNDEEP
mysqlsqlselectjoin
Michael
Michael

Reputation: 14248

MySQL Where Not Exists

What's wrong with this code? I keep getting ERROR 1064 (42000): You have an error in your SQL syntax

SELECT clientReport.id
  WHERE clientReport.rowNumber NOT EXISTS (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

Upvotes: 0

Views: 230

Answers (5)

cbroughton
cbroughton

Reputation: 1856

What is the full error message MySQL server is returning? You should get an error message like the below:

You have an error in your SQL syntax near `NOT EXISTS`

You should also consider using a RIGHT JOIN instead of a sub-query select, as a RIGHT JOIN seems to be what you want in this case.

EDIT: Additionally, because of the performance hit observed when sub-queries are run, it's advisabe to use very selective JOINs instead, that being said, it will be okay to use sub-queries again in normal queries when MySQL GA implements the use of LIMIT in sub-queries. This will reduce the performance hit greatly.

Upvotes: 1

Harry Joy
Harry Joy

Reputation: 59694

You forgot to add from clause in main query.

SELECT clientReport.id from clientReport
  WHERE clientReport.rowNumber NOT IN (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

Upvotes: 1

Zento
Zento

Reputation: 335

I assume you want to do something like:

SELECT clientReport.id 
FROM clientReport 
LEFT JOIN report02 ON(report02.id = clientReport.id)
WHERE report02.id is null;

This will return all IDs from clientReport which have no corresponding entry in report02.

An alternative might be:

SELECT clientReport.id FROM clientReport
WHERE clientReport.rowNumber NOT IN (
  SELECT clientReport.rowNumber 
  FROM report02, clientReport
  WHERE report02.id=clientReport.id);

Upvotes: 2

Tim Rogers
Tim Rogers

Reputation: 21723

You probably want NOT IN instead of NOT EXISTS

Upvotes: 1

Mutation Person
Mutation Person

Reputation: 30520

You're missing a FROM on your first select statement:

SELECT clientReport.id
  FROM clientReport '<--- need this
  WHERE clientReport.rowNumber NOT EXISTS (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

Upvotes: 1

Related Questions