EXIST / IN / and subqueries does not work

[pingLeeQuan]

I have been trying to get this working for a while but with no use. I consulted the manual and even the JDBC manual which was developed by Mysql and all say the same think. Every time i try to use the phrase "EXIST" or "IN" or "NOT IN" or even using sub query, it fails telling me that i have a syntax error near .... (the above options).

I am using Java and employing the JDBC driver from Mysql.

The mysql server runs 3.2.3
I test all queries in Mysql Control Center (the one that i use to test my SQL statements) before deploying in code

Sample sql
SELECT PersonName FROM tPerson t where t.PersonName EXISTS (select tPersonName from tPerson_Exception)

The error is
" You have an error in your SQL syntax near 'EXISTs (select PersonName from tPerson_Exception)' at line 1"


The manual for all 3 products say they should work but I cannot get any of them to work.

What am I doing wrong?

Thanks in advance for your help

 

[towerbase]

I understand that sub selects came in at MySQL version 4.1 so EXISTS won't work in MySQL version 3.2.3 as it depends on sub selects.

Andrew
Hampshire, UK

 

[pingLeeQuan]

Thanks Andrew, I guess it is time to upgrade.

 

[ericbrunson]

If you upgrade using the RPMs available from dev.mysql.com, the upgrade is totally painless, I've done it on a dozen machines. Still, there's no reason not to do a backup beforehand.

 

[pingLeeQuan]

yes... the problem is I am running apache 3x and it gave me a difficult time to upgrade. After 2 days of downloads and debuging, i rolled back to Mysql3.2.3.
The problem is i am having such a hard time with Mysql 3.

I have 2 tables and they contain similar data on 3 fields. I tried to join on a field which is similar in structure and data in both tables, but the query still does not work.
It is embracing to list this query but here it is... it is very simple and I know I am over looking something very simple.

select tTemp_Person.PersonName from tTemp_Person INNER JOIN tPerson ON (tPerson.PersonNumber <>tTemp_Person.PersonNumber)

All i want is teh record(s) that are in one and not the other. This query returns all records. How can i get only the records which exist in one table and not the other?

TIA


thanks.

 

[azzazzello]

select  tTemp_Person.PersonName   from tTemp_Person LEFT JOIN tPerson ON (tPerson.PersonNumber = tTemp_Person.PersonNumber) WHERE  tPerson.PersonNumber IS NULL

This should give you all tTemp_Person.PersonName where a tTemp_Person.PersonNumber does not exist in tPerson table
(untested)