CODEWITHSUNDEEP
mysqlsql
Arbiz
Arbiz

Reputation: 182

MySQL records that matches multiple criterias

There is probably a simple answer to this but I can't seem to find it anywhere..

My table looks like this:

item_id     tag_id
----------------------
100          55
101          55
100         320
102         320
100          77
100         489
101         200

Asking for items with tags 55 AND 320 should return item_id = 100 (but not 101 or 102 as they don't have both tags)

Upvotes: 1

Views: 44

Answers (3)

Abhay
Abhay

Reputation: 413

You can try:

SELECT item_id
FROM table_name
WHERE tag_id IN (55,320)
GROUP BY item_id
HAVING COUNT(DISTINCT tag_id) > 1;

It will return item_id that have more than one distinct tag_id.

Pass your tags in IN by comma separated as many as you want.

Upvotes: 2

ShawnOrr
ShawnOrr

Reputation: 1329

SELECT item_id
  FROM Table1
  WHERE tag_id IN (55,320)
  GROUP BY item_id
  HAVING COUNT(DISTINCT tag_id) > 1

Fiddle example

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use HAVING clause:

SELECT item_id
FROM tab_name
WHERE tag_id IN (1,2)
GROUP BY item_id
HAVING COUNT(DISTINCT tag_id) = 2;

SqlFiddleDemo

or even:

SELECT item_id
FROM tab_name
GROUP BY item_id
HAVING GROUP_CONCAT(DISTINCT tag_id ORDER BY tag_id ) = '1,2';

SqlFiddleDemo2

Upvotes: 4

Related Questions