[RESOLVED] If/then statement in WHERE on a SELECT query?

gwerner

Is it possible to do a select statement that checks a column field for a particular value? If it finds this value then it returns that single row. If not, then it looks for a default value in the same column. If it finds one, it returns that row. If not, then the query returns nothing and no records are found. This may be a simple task, but my mind seems to be trying to overcomplicate this. I thought this should be doable from a simple query statement, but I'm drawing a blank. Thanks for any direction someone points me in.

bradgrafelman

WHERE colName = 'particular value' OR colName = 'default value'

EDIT: or even...

WHERE colName IN ('value 1', 'value 2', 'value 3', 'etc.')

gwerner

I did actually try this before my post. It doesn't work like I'm wanting it to. Using your second example. It is skipping past the row with value of 1 and only returning the row with colName value of 2. I would like it to stop and return the row if it finds a colName with value of 1. If it doesn't find colName with 1, then continue to look for colName with value of 2. If it finds a row return it. If not return nothing. Not sure what I'm doing wrong here?

bradgrafelman

Unless you add an appropriate 'ORDER BY' statement, the results aren't going to be in any particular order... so you may get the 'value 3' row, followed by 'value 1', followed by 'value 2'.

Sounds like you should simply add an ORDER BY statement (if applicable) and perform the logic in the application to determine which row of the result set (if any) to use.

gwerner

That worked. I guess I was thinking that the order wouldn't matter if I was specifically selecting. My thoughts were that I'd be doing an if/else type of selection, so order wouldn't matter. But, I was wrong. Thanks for the help.

Weedpacket

gwerner wrote:
My thoughts were that I'd be doing an if/else type of selection, so order wouldn't matter.

You'd be right: order wouldn't matter (it doesn't in a relational database). You'd get all the records that match the condition, no matter what order they're in.