Page 1 of 1

I want to use an IF MySQL statement on this query

Posted: Thu Mar 11, 2010 4:38 am
by impulse()
Hi,

I have the following query, which at the moment I use a union query to identify a true false condition. I want to use something other than union for doing this because I don't want a similar query running twice.

Code: Select all

SELECT *, 'old' as ident
FROM table1
WHERE col1 < 1234567891
AND col2 = 'O'
AND col3 != 1
UNION
SELECT *, 'new' as ident
FROM table1
WHERE col2 = 'O'
AND col3 = 1
HAVING ID != 29394
As you can see, it's running a very similar query twice. Can this be put into a MySQL IF condition and if so could somebody please explain to me how. I've used MySQL IFs before but that's only using single conditions.

Stephen,

Re: I want to use an IF MySQL statement on this query

Posted: Thu Mar 11, 2010 4:50 am
by Eran
Use a case statement:
[sql]SELECT *, (CASE WHEN col3=1 THEN 'new' ELSE 'old' END) AS identFROM table1WHERE col2 = 'O'[/sql]

(Adjust your filters accordingly)

Re: I want to use an IF MySQL statement on this query

Posted: Thu Mar 11, 2010 5:00 am
by impulse()
Hi,

That doesn't seem to cover the 2 sets of conditions that I have.

For example, the results should either be true to the one group of conditions or true to the other group to be included in the result set.

Each row should either be true to
1) col1 < 1234567891
AND
2) AND col3 != 1

OR

1) col3 = 1

And col2 = 'O' is a universal condition.

I'm not too interested in field values from the select. The ident column can be removed from the SQL to simplify what I'm trying achieve, so I'm only doing SELECT * now.

Hopefully that makes sense and that I haven't misunderstood your example.

Stephen,

Re: I want to use an IF MySQL statement on this query

Posted: Thu Mar 11, 2010 5:01 am
by Eran
You didn't misunderstand, you just didn't read it all the way through.
(Adjust your filters accordingly)
Put your conditions inside the case statement.
[sql]...(CASE WHEN col3=1 AND col2='bar' AND col4='baz' ... THEN 'new' ELSE 'old'  END) AS ident ...[/sql]

Re: I want to use an IF MySQL statement on this query

Posted: Thu Mar 11, 2010 5:04 am
by impulse()
pytrin wrote:You didn't misunderstand, you just didn't read it all the way through.
(Adjust your filters accordingly)
Put your conditions inside the case statement.
[sql]...(CASE WHEN col3=1 AND col2='bar' AND col4='baz' ... THEN 'new' ELSE 'old'  END) AS ident ...[/sql]
I understand now :)

Thank you, Stephen

Re: I want to use an IF MySQL statement on this query

Posted: Thu Mar 11, 2010 5:33 am
by impulse()
Infact, I didn't understand as well as I thought. You example seems to be based around the select values. I have re-written my query in an easier example to show what I'm trying to achieve. The reason I'm looking for an alternative query is because this looks like an ugly way to do it.

Code: Select all

SELECT *
    FROM table
    WHERE
    (
        col1 < 1268739037
        AND
        col2 != 1
        AND
        col3 = 'O'
    )
    OR
    (
        col2 = 1
        AND
        col3 = 'O'
    )
I'm starting to think that an IF condition couldn't be used in this situation. If you can suggest an alternative way to do this then please let me know.

Stephen,

Re: I want to use an IF MySQL statement on this query

Posted: Wed Mar 17, 2010 8:48 am
by batfastad
I'm no expert but that looks like the best way to me. I'll keep an eye on this to see if anyone says otherwise
Cheers, B