CODEWITHSUNDEEP
phpsqlmysqlcodeigniter
Dan LaManna
Dan LaManna

Reputation: 3501

Chaining queries - Can't get AND/OR working

After analyzing my issue, I think I may need to use the "union" operator in SQL, however I'm unsure. Anywho I'm chaining my queries in codeigniter to come out with this query:

SELECT *
FROM (`deals`)
WHERE `category` = 'books'
AND `host` = 'amazon'
OR `host` = 'zappos'
OR `host` = 'newegg'
ORDER BY `time` desc

So it selects things that aren't in the category books, but are from those hosts. So I'm looking to make it so it returns only results in books, AND have a host of any of those three. I would prefer doing this with a database query rather than manually filtering afterwards. I am using MySQL with an innoDB table. Thanks

Upvotes: 1

Views: 162

Answers (2)

Ken Redler
Ken Redler

Reputation: 23943

Try some parentheses:

SELECT *
FROM (`deals`)
WHERE `category` = 'books'
AND ( 
`host` = 'amazon'
OR `host` = 'zappos'
OR `host` = 'newegg'
)
ORDER BY `time` desc

Upvotes: 0

judda
judda

Reputation: 3972

Use brackets to make it work as desired.

SELECT *
FROM (`deals`)
WHERE `category` = 'books'
AND ( `host` = 'amazon'
OR `host` = 'zappos'
OR `host` = 'newegg' )
ORDER BY `time` desc

Or use the 'IN' keyword:

SELECT *
FROM (`deals`)
WHERE `category` = 'books'
AND `host` IN ( 'amazon', 'zappos', 'newegg' )
ORDER BY `time` desc

Upvotes: 10

Related Questions