CODEWITHSUNDEEP
mysqlselectconditional-statements
user316602
user316602

Reputation:

Boolean Operators in MySQL

I have list of items (separated by comma) that I need to look up in a database. Initially I was looking up each item code individually, but there must be an easier way of doing it.

I was playing around in phpMyAdmin trying to select items with no luck.

SELECT * 
FROM  `items` 
WHERE  `code` = ( 20298622
OR 83843296 
OR 46549947 )

Returned no results.

SELECT * 
FROM  `items` 
WHERE  `code` =20298622
OR 83843296 
OR 46549947

Returned every item in the database.

I was reading up on the MySQL docs, and it appears that OR (or ||) should do what I'm looking for. Where did I go wrong?

Also, FWIW, code is a integer field.

Thanks!

Upvotes: 2

Views: 250

Answers (3)

alex
alex

Reputation: 490213

You could use...

SELECT * 
  FROM `items` 
 WHERE `code`
    IN (20298622,
        83843296, 
        46549947)

Upvotes: 4

Xint0
Xint0

Reputation: 5389

You are not using the correct syntax. You should change the statement to:

SELECT * 
FROM `items` 
WHERE `code` = 20298622
    OR `code` = 83843296 
    OR `code` = 46549947

Or as @alex notes on his answer, you can use the IN clause:

SELECT *
FROM `items` 
WHERE `code` IN (20298622, 83843296, 46549947)

Upvotes: 1

IAmTimCorey
IAmTimCorey

Reputation: 16757

The reason it failed is because the OR statement separates two conditional statements. Therefore, your first conditional statement would evaluate (code = 20298622) but then your next statement is simply a number. If you want to use the OR statement, you will need to specify the column name again. Here is what it would look like:

SELECT * 
FROM  `items` 
WHERE  `code` = 20298622
OR `code` = 83843296 
OR `code` = 46549947

You could also use more complex statements like IN or you could even do a join to a second table (even a temp table). Basically, there are a lot of ways to accomplish this. You will just need to choose which one fits your situation best (speed, ease of use, etc.)

Upvotes: 2

Related Questions