CODEWITHSUNDEEP
mysqlselect
xylar
xylar

Reputation: 7673

Select a value based on multiple rows in MySql

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.

Upvotes: 1

Views: 306

Answers (4)

Mahesh
Mahesh

Reputation: 2256

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)

Upvotes: 0

GG.
GG.

Reputation: 21904

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

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

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425833

Assuming that (docId, tagId) combination is unique:

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

Upvotes: 1

Related Questions