CODEWITHSUNDEEP
sqlmysql
Phill Pafford
Phill Pafford

Reputation: 85358

MySQL Queries: find any record with a PENDING status and find any records with a FAILED status

I have three columns with three values that can be set for each column

Column_1 | column_2 | column_3
________ | ________ | ________
COMPLETED| FAILED   | PENDING
COMPLETED| COMPLETED| COMPLETED
FAILED   | COMPLETED| COMPLETED
COMPLETED| PENDING  | COMPLETED
COMPLETED| COMPLETED| PENDING
COMPLETED| COMPLETED| COMPLETED
COMPLETED| COMPLETED| COMPLETED
PENDING  | COMPLETED| COMPLETED

Looking for two queries, One to find any record with a PENDING status and the other to find any records with a FAILED status

NOTE: PENDING and FAILED should have the same logic

Query: PENDING
SELECT * FROM tbl
WHERE Column_1 = 'PENDING' OR Column_2 = 'PENDING' OR Column_3 = 'PENDING'

Query: FAILED
SELECT * FROM tbl
WHERE Column_1 = 'FAILED' OR Column_2 = 'FAILED' OR Column_3 = 'FAILED'

These queries are not pulling the correct records. I think it's matching the first condition in the WHERE clause and then doing the OR clause as a separate condition. I've tried a couple variations but still no luck.

Alt Query: 
SELECT * FROM tbl
WHERE Column_1 OR Column_2 OR Column_3 = 'PENDING'

So for the PENDING status, the query should return 4 rows for the data grid above and for the FAILED status, the query should return 2 rows for the data grid above

Upvotes: 0

Views: 2046

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562721

SELECT * FROM tbl
WHERE Column_1 OR Column_2 OR Column_3 = 'PENDING'

This is definitely not going to return what you think. Here's how it's evaluated:

SELECT * FROM tbl 
WHERE (Column_1) OR (Column_2) OR (Column_3 = 'PENDING')

Using a string column as a truth term evaluates the string as an integer, which in these examples is 0, interpreted as false. So this is like:

WHERE (false) OR (false) OR (Column_3 = 'PENDING')

The effect is that it ignores the first two columns and returns rows only where the third column matches 'PENDING'.

In other words, you gave a query that is syntactically valid but doesn't have the result you intend.

The first two queries you gave do return 4 rows where any column is 'PENDING' and two rows where any column is 'FAILED'. I just tested this and it works. If it doesn't match your expectations, perhaps you can clarify:

  • What query did you try?
  • What was returned?
  • What result were you expecting instead?

Upvotes: 0

Xaisoft
Xaisoft

Reputation: 46641

Try wrapping parenthesis around each condition to see if that works:

SELECT * FROM tbl
WHERE (Column_1 = 'PENDING') OR (Column_2 = 'PENDING') OR (Column_3 = 'PENDING')

I am actually not familiar with mysql, but this might be a better way, anyone can correct me if I am wrong:

SELECT * FROM tbl WHERE 'PENDING' IN(Column_1,Column_2,Column_3,Column_4)

Again, if the above is incorrect, I apologize.

Upvotes: 2

Satanicpuppy
Satanicpuppy

Reputation: 1587

If the behaviour is weird, I'd try parenthesis. 

Query: PENDING
SELECT * FROM tbl
WHERE ((Column_1 = 'PENDING') OR (Column_2 = 'PENDING') OR (Column_3 = 'PENDING'))

Query: FAILED
SELECT * FROM tbl
WHERE ((Column_1 = 'FAILED') OR (Column_2 = 'FAILED') OR (Column_3 = 'FAILED'))

Though, I'll be honest, I don't know why that would work better/worse: since all you're using is "OR" it shouldn't matter.

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171509

Your first query looks correct. It is possible that you are using char datatype and this is confusing things, i.e., you may need to add trailing spaces. Can you post your schema?

Upvotes: 1

Related Questions