LinuxQuestions.org - mysql if exists

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - mysql if exists (https://www.linuxquestions.org/questions/linux-server-73/mysql-if-exists-4175455602/)

hello all,

I have a bit of a strange mysql query that I am trying to run, basically the pseudo code would be:

Code:

select c.id, c.name 

from companies c

where 1 = 1

and if exists(select count(*) from addresses where address = 'variable') 

    then

      and c.name = 'variable'

    else

      and c.id in (select id from another_table);

    end if;

Good to know. Glad you don't need any help with anything...

:-)

Seriously, is there a question in there somewhere? Are you asking a question???

Hello, yes sorry the question is; how can I get that statement to work? Is there a way?

there are control flow functions in mysql:
http://dev.mysql.com/doc/refman/5.0/...functions.html

You may want to exchange the "5.0" in the link with the mysql version you are using.

Hi Cheers, I have taken a look but I am still unsure as to use it the the way that I am attempting, I dont suppose you have an example that I can use do you? Many thanks in advance....

I'm working with scripting languages as PHP or Perl so I don't have examples that could be useful for you, sorry.

trscookie,

One of the problems with pseudo-code is that it has no defined syntax, so it is perhaps too open to interpretation. :-)

mysql has EXISTS which can be negated as NOT EXISTS, also if statements, a case statement, which is almost like a switch statement in some languages, or a case construct in bash.

If I'm thinking correctly, and if I'm interpreting your pseudo-code correctly, I would focus on a simplified form of the conditional part of it:

Code:

if TRUE

    then

      and c.name = 'variable'

    else

      and c.id in (select id from another_table);

    end if;

I expressed it that way, to emphasize this, if the EXISTS clause is true, in a way, the clauses controlled by the if seem to evaluate to this:

Code:

and c.name = 'variable'

if false, then this clause is the result:

Code:

and c.id in (select id from another_table)

So, if the EXISTS clause is true, overall that would appear to be equivalent to this:

Code:

select c.id, c.name 

from companies c

where 1 = 1

and exists (select count(*) from addresses where address = 'variable')

and c.name = 'variable' ;

whereas if false, overall it would seem to be equivalent to this:

Code:

select c.id, c.name

from companies c

where 1 = 1

and not exists (select count(*) from addresses where address = 'variable')

and c.id in (select id from another_table) ;

I created a test database, and tables with column types that should be compatible with your SQL. With that, the last two statements I illustrated above, actually run on mysql. But from that, I'm not really clear about exactly what you are trying to accomplish.

If the statements I illustrated help you at all, then great. If they don't help, perhaps you could provide a textual, not pseudo-code description, with the specifics of what you are trying to accomplish.