Mysql Complex Where Clause
I really surprised when I tried below code in MySQL:
SELECT * FROM table
WHERE (key='free_shipping' and value='yes')
AND (key='price' and value='5')
It doesn't work. I need to get product that is both free_shipping is 'yes' AND price equal '5' at the same time. How can I create this query properly?
Table Structure:
contents: (TABLE 1)
CONTENT_ID TITLE DESCRIPTIO开发者_如何学CN DATE
content_fields: (TABLE 2)
FIELD_ID CONTENT_ID KEY_NAME VALUE
Example (Get product that has 1 ID and its shipping is FREE):
SELECT * FROM `contents` as c LEFT JOIN `content_fields` as cf ON c.content_id = cf.content_id WHERE c.content_id = 1 AND cf.key_name = 'free_shipping' AND cf.value = 'yes'
Your current query is contradictory as a single row can never match the WHERE clause. I assume you want something like this.
SELECT product_id
FROM table
WHERE (key='free_shipping' and value='yes') or (key='price' and value='5')
GROUP BY product_id
HAVING COUNT(DISTINCT key) = 2
Try this:
WHERE free_shipping='yes' AND price='5'
or:
WHERE free_shipping=yes AND price=5
if the fields aren't strings
The brackets don't change the meaning of this statement. Without them you have:
key='free_shipping' and value='yes' and key='price' and value='5'
=>
key='free_shipping' and key='price'
=>
nothing!
try WHERE free_shipping='yes' AND price='5'
edit: scrap that, I'm now completely confused by your table. Is it just a load of key/value pairs? Could we see the table structure?
To get product titles that have free shipping and worth 5....... select distinct x.TITLE from contents x, content_fields y, content_fields z where x.CONTENT_ID = y.CONTENT_ID and y.CONTENT_ID = z.CONTENT_ID and y.key_name = 'free_shipping' and y.value='yes' and z.key_name = 'price' and z.value='5'
精彩评论