mysql if exists

trscookie · 03-25-2013, 11:20 PM

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;

rigor · 03-26-2013, 12:33 AM

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???

trscookie · 03-26-2013, 01:09 AM

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

j-ray · 03-26-2013, 02:30 AM

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.

trscookie · 03-26-2013, 04:04 AM

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....

j-ray · 03-26-2013, 05:04 AM

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

rigor · 03-28-2013, 09:48 PM

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.