CODEWITHSUNDEEP
mysqlsqlrows
Agu Dondo
Agu Dondo

Reputation: 13569

Selecting rows with multiple values for a column

On MySQL, Given the following table (image_tags):

image_id | tag_id
-----------------
1        | 100
1        | 20
2        | 100
3        | 20

I would like to select the rows where tag_id=100 AND tag_id=20, my desired result in this case would be:

image_id
--------
1

I tried but I couldn't find a proper way to do it in pure SQL. Thanks!

Upvotes: 0

Views: 122

Answers (7)

Nisarg
Nisarg

Reputation: 3252

You can use

select image_id from test
where image_id IN (select image_id from test
where tag_id = '100') AND tag_id = '20'

check Demo

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

Try this one:

SELECT image_id FROM MyTable
WHERE tag_id IN (100,20)
GROUP BY image_id
HAVING COUNT(*) = 2

If you want to select every rows then try this one:

SELECT * FROM MyTable
WHERE image_id IN
(
    SELECT image_id FROM MyTable
    WHERE tag_id IN (100,20)
    GROUP BY image_id
    HAVING COUNT(*) = 2
)

See this SQLFiddle

Upvotes: 1

dijkstra
dijkstra

Reputation: 1078

select image_id 
from image_tags 
where image_id IN (
            select image_id 
            from image_tags 
            where tag_id = 100) 
AND tag_id = 20

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Simple JOIN query

SELECT a.image
FROM image_tags a
INNER JOIN image_tags b ON a.image_id = b.image_id AND b.tag_id = 20
WHERE a.tag_id = 100

or using counts, but coping if there are duplicates tags for an image

SELECT DISTINCT image_id
FROM image_tags
WHERE tag_id in ('20', '100')
GROUP BY image_id
HAVING count(DISTINCT tag_id) = 2

Upvotes: 1

cheeyos
cheeyos

Reputation: 681

Assuming the (image_id, tag_id) pair is unique:

SELECT image_id
FROM image_tags
WHERE tag_id IN ('20', '100')
GROUP BY image_id
HAVING count(*) = 2

Upvotes: 3

John Faben
John Faben

Reputation: 151

SELECT Image_ID FROM 
(SELECT Image_ID FROM tab
WHERE Tag_ID = 100) tag100
JOIN
(SELECT Image_Id FROM tab
WHERE TagID = 20) tag20

I think this is what you're looking for you - you want those records which have both a TagID that is equal to 20 and a TagID equal to 100, right?

Upvotes: 0

user2572367
user2572367

Reputation: 207

what you did is right you can also try this

select * from image_tags where tag_id in ('100','20',....)

Upvotes: 0

Related Questions