Select a value based on multiple rows in MySql

Question

If I have the following example table:


id   docId   tagId
1     12      2
2     13      2
3     13      3
4     13      4
5     14      3

How can I select the docId where the tagId is both 2 and 3, i.e. docId = 13.

Joe Stefanelli · Accepted Answer · 2011-03-14 16:14:26Z

2

select docId
    from YourTable
    where tagId in (2,3)
    group by DocId
    having count(distinct tagId) = 2

answered Mar 14, 2011 at 16:14

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quassnoi · Accepted Answer · 2011-03-14 16:13:54Z

1

Assuming that (docId, tagId) combination is unique:

SELECT  docId
FROM    mytable
WHERE   tagId IN (2, 3)
GROUP BY
        docId
HAVING  COUNT(*) = 2

answered Mar 14, 2011 at 16:13

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

GG. · Accepted Answer · 2011-03-14 16:15:08Z

0

SELECT docId
FROM my_table
WHERE tagId >= 2
AND tagId <= 3;

answered Mar 14, 2011 at 16:15

GG.

22k14 gold badges93 silver badges133 bronze badges

Mahesh · Accepted Answer · 2011-03-16 11:03:36Z

0

use Self Join.

SELECT DISTINCT t1.docId 
    FROM test AS t1 
    INNER JOIN test AS t2
    ON (t1.docId = t2.docId and t1.tagId =2 AND t2.tagId =3)

answered Mar 14, 2011 at 16:39

Mahesh

2,27617 silver badges17 bronze badges