CODEWITHSUNDEEP
mysqlselect
Yury Lankovskiy
Yury Lankovskiy

Reputation: 153

MySQL logical operation SELECT query

I have the following query.

SELECT 
    f.id, f.food_name, tm.resource_id, tm.tag_id, t.name
FROM 
    tag as t, tagmap as tm
JOIN 
    item as f 
    ON 
    (
    f.id = tm.resource_id AND tm.tag_id = 
        (
        SELECT 
            t.tag_id 
        FROM 
            tag as t 
        WHERE 
            t.name LIKE '%meat%' OR t.name LIKE '%vegan%'
        ) 
    )
GROUP by f.id

I believe the error is in line 

f.id = tm.resource_id AND tm.tag_id =

It is looking for a single tag_id and receiving many thus the error: Error executing the SQL statement

What is the procedure to receive an array of tag_id 's? I know this statement works for a single ID because if I remove the second WHERE condition the query works.

Upvotes: 3

Views: 370

Answers (1)

Bohemian
Bohemian

Reputation: 425003

Use the IN keyword:

...
AND tm.tag_id IN -- Changed  = to IN here
    (
    SELECT 
        t.tag_id 
    FROM 
        tag as t 
    WHERE 
        t.name LIKE '%meat%' OR t.name LIKE '%vegan%'
    ) 
...

The basic problem you had was that the select would return multiple rows, and when it did, the statement exploded because = can only handle one value.

Upvotes: 2

Related Questions