Exists -- ?

shawnlui · #1 (**permalink**) 06-24-04, 03:09

Select field1 from table1 where exists (select fields1from table2);

The above SQL generates error: "#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS( SELECT field1 FROM table2 ) LIMIT 0, 30' at line 3

But the sql is an example in MySQL manual and it passed the test on MS ACCESS. I also tried to change 'exists' to
'field1 in' or 'field1 = any' or 'fields is ', none of them works.

Any idea?

Shawn

Pat Phelan · #2 (**permalink**) 06-24-04, 06:37

Depending on which version of MySQL you are using, the EXISTS clause may not be supported. In almost any version you could use:

Code:

SELECT a.field1
   FROM table1 AS a
   INNER JOIN table2 AS b ON (b.field1 = a.field1)
   LIMIT 0, 30;

-PatP

r937 · #3 (**permalink**) 06-24-04, 06:54

don't forget to add DISTINCT to pat's query when there's a one-to-many relationship between table1 and table2

Pat Phelan · #4 (**permalink**) 06-24-04, 09:05

Good point! Thanks Rudy.

-PatP

shawnlui · #5 (**permalink**) 06-24-04, 11:34

Thank you so much to both of you. Your query works. What is the meaning of 'limit 0, 30'?

BTW, the sql actually has DISTINCT but I removed it for asking the question.

thanks again.
Shawn

shawnlui · #6 (**permalink**) 06-24-04, 11:37

I know the meaning of 'limit 0, 30' now.

thanks;
Shawn

Internet Services

Web Development

Digital Marketing

Consumer Tech