What is wrong with this query?

[vigour]

I want to search for pictures that matches the following:

motifcategory_id 6 and keyword_id 6 and archive_id 295

motifcategory_id 6 and keyword_id 22 and archive_id 294

motifcategory_id 6 and keyword_id 71 and archive_id 296

So I use this query:

SELECT product_id, small_jpg_path FROM product WHERE motifcategory_id = 6 AND keyword_id LIKE '%,6,%' OR keyword_id LIKE '%,22,%' OR keyword_id LIKE '%,71,%' AND archive_id IN (295,294,296) ORDER BY tif_path ASC

But this will give me the wrong answer. Can someone please tell me how to wright this query?

[bradgrafelman]

I'd say it's your DB schema that is 'wrong' instead of just the query.

Normalize your DB schema first. The 1st normal form says that you have no multivalued attributes. In other words, no comma-separated list of ID's.

[Keepanitreel]

vigour;10981119 wrote:

I want to search for pictures that matches the following:

motifcategory_id 6 and keyword_id 6 and archive_id 295

motifcategory_id 6 and keyword_id 22 and archive_id 294

motifcategory_id 6 and keyword_id 71 and archive_id 296

So I use this query:

SELECT  product_id, small_jpg_path FROM product WHERE motifcategory_id = 6 AND keyword_id LIKE '%,6,%' OR keyword_id LIKE '%,22,%' OR keyword_id LIKE '%,71,%' AND archive_id IN (295,294,296) ORDER BY tif_path ASC

But this will give me the wrong answer. Can someone please tell me how to wright this query?

I am guessing that keyword_id data is stored as comma sep values like 1,2,3,4 etc. Prob with your query is if you are searching for keyword_id 22 using %,22,% and the stored data reads 22,23,24 you will not get the result from this row. So I suggest (if you can't normalize your data to use) this sql

SELECT 
 product_id, 
 small_jpg_path 
FROM product 
WHERE motifcategory_id = 6 
AND 
(select FIND_IN_SET(6, keyword_id ) 
OR 
(select FIND_IN_SET(22, keyword_id ) 

[BrightIdeas]

Have you tried adding parentheses to force operator precedence?

SELECT product_id, small_jpg_path 
FROM product 
WHERE (motifcategory_id = 6)
  AND (keyword_id LIKE '%,6,%' 
         OR keyword_id LIKE '%,22,%' 
         OR keyword_id LIKE '%,71,%')
  AND (archive_id IN (295,294,296)) 
ORDER BY tif_path ASC

I think here's what the computer saw in your original form:

SELECT product_id, small_jpg_path 
FROM product 
WHERE (motifcategory_id = 6 AND keyword_id LIKE '%,6,%') 
  OR keyword_id LIKE '%,22,%' 
  OR (keyword_id LIKE '%,71,%' AND archive_id IN (295,294,296)) 
ORDER BY tif_path ASC

AND always gets validated before OR. I think you are dealing with a simple matter of operator precedence.