CODEWITHSUNDEEP
sqldatabasepostgresqlwhere-clause
sg552
sg552

Reputation: 1543

Querying with WHERE, AND & ILIKE

I have this 3 column:

enter image description here

When I'm using this query:

SELECT "code", "desc", "lt_status_uuid" FROM "product_info" 
WHERE 
"lt_status_uuid" = '1f9d541b-5812-4d81-a997-6fb15ed397b1'
AND
"desc" ILIKE 
'%nk%' OR "code" ILIKE '%nk%';

I will get 1 record even though lt_status_uuid 1f9d541b-5812-4d81-a997-6fb15ed397b1 does not exist in my table. I was actually expecting no record returned.

How can I query based on lt_status_uuid and if it's exist; the ILIKE will trigger?

Thanks in advance.

Upvotes: 0

Views: 750

Answers (1)

Ken White
Ken White

Reputation: 125669

You need parentheses because of the precedence of AND and OR. AND has higher precedence, and is evaluated first. Because of that, your code is being interpreted as

SELECT "code", "desc", "lt_status_uuid" FROM "product_info" 
WHERE 
("lt_status_uuid" = '1f9d541b-5812-4d81-a997-6fb15ed397b1'
AND
"desc" ILIKE 
'%nk%') OR "code" ILIKE '%nk%';

Adding parentheses clarifies what you're asking, so that the evaluation order is what you actually want:

SELECT "code", "desc", "lt_status_uuid" FROM "product_info" 
WHERE 
"lt_status_uuid" = '1f9d541b-5812-4d81-a997-6fb15ed397b1'
AND
("desc" ILIKE 
'%nk%' OR "code" ILIKE '%nk%');

Upvotes: 5

Related Questions