advanced nested SQL question

[leegold]

Given:
mysql> show tables;
+-------------------+
| Tables_in_balloon |
+-------------------+
| balloon_rec |
| balloon_txt |
+-------------------+
mysql> show columns from balloon_txt;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| access_no | varchar(20) | | PRI | | |
| recs_txt | text | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.22 sec)

mysql> show columns from balloon_rec;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| access_no | varchar(20) | | PRI | | |
| title | varchar(210) | YES | | NULL | |
| author | varchar(150) | YES | | NULL | |
| doc_date | date | YES | | NULL | |
| elec_access | varchar(75) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
5 rows in set (0.14 sec)

I want to do a FULLTEXT search in balloon_txt - have the SQL give me the matched access_no(s), and then use those access_no(s) to select the same matching access_no(s) ie. the matching records in balloon_rec. I tried some SQL but I need help, access_no and recs_txt where "fulltext indexed "together". Thanks:

mysql> SELECT access_no FROM balloon_rec WHERE access_no IN (SELECT * FROM balloon_txt WHERE MATCH(access_no, recs_txt) AGAINST ('robin'));
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to you

 

[TonyGroves]

Sub-queries are not supported by MySQL until version 4.1 (currently alpha). Are you using an earlier version?

-----
ALTER world DROP injustice, ADD peace;

 

[leegold]

C:\>mysqladmin -V
mysqladmin Ver 8.40 Distrib 4.0.20a, for Win95/Win98 on i32

You're kidding me!!!
I'll upgrade!!!