CODEWITHSUNDEEP
mysql
Ashish Roy
Ashish Roy

Reputation: 15

SELECT on same table for Multiple key value pairs

I have a table like

id keyword_id value   category_id asset_id
 1          2 abc.jpg        4424      479
 2          3 Jpeg           4424      479
 3          4 400*600        4424      479
 4          2 def.jpg        4424      603
 5          3 Jpeg           4424      603
 6          4 500*700        4424      603

I want to fetch values depending on multiple pairs like (keyword id = 3 and value like '%Jpeg%') And (keyword id = 2 and value like '%abc%').

This should return only one value with asset_id 479 because it meets both the criteria.

I am running a query like 

SELECT DISTINCT asset_id FROM asset_keyword_table where category_id = 4424
AND (( keyword_id = 2 AND value LIKE '%abc%') AND ( keyword_id = 3 AND 
value LIKE '%Jpeg%'));

But EXPLAIN this query returns Impossible WHERE clause. What is the way to get this working. This query is generated by BE code so blocks likes this can be many -

( keyword_id = 2 AND value LIKE '%abc%')

depending on user input. And the blocks separated by AND or OR is also determined by User. Using aliases is not possible because there is no limit on the number of blocks.

Can anyone help?

Upvotes: 1

Views: 1416

Answers (2)

HvS
HvS

Reputation: 514

Nested queries should give you the desired result:

SELECT DISTINCT asset_id FROM asset_keyword_table WHERE 
( category_id = 4424 AND keyword_id = 2 AND value LIKE '%abc%' )
AND asset_id IN
( SELECT DISTINCT asset_id FROM asset_keyword_table WHERE 
   category_id = 4424 AND keyword_id = 3 AND value LIKE '%Jpeg%' )
/*  OR asset_id IN
( SELECT DISTINCT asset_id FROM asset_keyword_table WHERE
   category_id = 4424 AND keyword_id = 4 AND value LIKE '%500%' ) */

SQL Fiddle

Upvotes: 0

John Woo
John Woo

Reputation: 263733

You need to filter the total number of rows that match with your condition.

SELECT  Asset_ID
FROM    asset_keyword_table 
WHERE   category_id = 4424
        AND 
        (( keyword_id = 2 AND value LIKE '%abc%') 
        OR (keyword_id = 3 AND value LIKE '%Jpeg%'))
GROUP   BY Asset_ID
HAVING  COUNT(*) = 2 -- number of rows that matched the condition

Here's a Demo.

Upvotes: 2

Related Questions