SQL syntax with EXISTS

[sebes]

Is this correct ?

Select 1 where exists(select * from tablename2)

Or does it have to be like this

Select 1 FROM tablename1 where exists(select * from tablename2)

The question is "is FROM mandatory ?"

From my tests only the second one works but I would really like to have the first one work.

Thanks.

 

[r937]

surprise, the first one works too

it might not do what you actually want, but it works

and now we come to the gist of the problem

what do you really want? i mean, in real terms, not "table1" and "table2"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sebes]

I'm "translating" these from VFP, there are tons of similar statements and I was hoping I don't have to "insert" a "FROM" because in most cases the statement is just to determine if there are any records and there really is no "FROM" for the first part of the statement.

Now, what's the trick for making the first one work ?

I'm using MySQL in ANSI mode, would that interfere ?

Thanks.

 

[r937]

if all you want is to know whether tablename2 has any rows at all, then this should work fine --

SELECT 1 WHERE EXISTS ( SELECT * FROM tablename2 )

what happenes when you run this in ANSI mode?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sebes]

This line: select 1 where exists (select * from pers)

gives this message:

#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 'where exists (select * from pers)' at line 1

This line: select 1 from tbl where exists (select * from pers)

works fine and produces a line with "1" for every row found in PERS.

So the problem remains: HOW TO GET RID of the "FROM" in the first part of the command ?

SQL Server info:
================
•Server: localhost (localhost via TCP/IP)
•Server version: 5.5.11
•Protocol version: 10
•User: root@localhost
•MySQL charset: UTF-8 Unicode (utf8)

 

[r937]

looks like in ANSI mode you must specify a table

do you really need ANSI mode here?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sebes]

What mode are you using ?

I choose ANSI because it allows to use || as concatenation operator. This is very helpful since I have to convert a whole system where we already have thousands of SQL statements.

Thanks.

 

[r937]

may i ask under what circumstances would you want to run a query to tell you if a certain table had any rows or not?

why wouldn't it?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sebes]

This is a huge system and there are many situations where such a query is necessary. I don't think it would help here to go into any details.

What I need at this point is to be able to run the queries like

select 1 where exists(select * from tablename where condition).

What should I configure to make this work ?

Is this related to ANSI mode ?

Thanks.

 

[r937]

Is this related to ANSI mode ?

yup

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sebes]

Ok. How is it related ?

How can I make it work ?

Can you give a sample where I can see this working ?

 

[r937]

do you know how to turn ANSI mode off?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sebes]

Well, I think I did turn it off, then tried again with same results.

I did this: SET GLOBAL sql_mode = ""

NOW, I ask questions here because I don't know many things and I want to learn. Someday, when I'll know more, maybe I'll also answer questions here, because this is the purpose of this site.

YOU DON'T have to answer my questions but if you want to answer, then please answer clearly NOT by other questions or by giving "hints".

Thank you.