I want to use an IF MySQL statement on this query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

I want to use an IF MySQL statement on this query

Post 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,
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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)
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

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

Post 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,
Last edited by impulse() on Thu Mar 11, 2010 5:02 am, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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]
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

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

Post 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
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

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

Post 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,
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
Post Reply