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