CODEWITHSUNDEEP
mysqlsqlselect
Jamie Poitra
Jamie Poitra

Reputation: 441

MYSQL: Select ids from table where multiple values match a single column at least twice

I've got a table that looks like this:

+----+--------------------------------+
| id | slug                           |
+----+--------------------------------+
|  1 | gift                           |
|  1 | psychological-manipulation     |
|  1 | christmas                      |
|  1 | giving                         |
|  1 | the-town-santa-forgot          |
|  1 | santa-claus                    |
|  1 | mp3                            |
|  1 | christmas                      |
|  2 | entertainment-culture          |
|  2 | christmas                      |
|  2 | culture                        |
|  2 | literature                     |
|  2 | christmas-music                |
|  2 | christmas-window               |
|  2 | broadcasting-nec               |
|  2 | how-the-grinch-stole-christmas |
|  2 | the-polar-express              |
|  2 | banker                         |
|  2 | christmas                      |
|  2 | potter                         |
|  2 | christmas-eve                  |
|  2 | bailey                         |
|  2 | its-a-wonderful-life           |
|  2 | the-polar-express              |
|  2 | disney                         |
|  2 | tim-burton                     |
|  2 | a-christmas-carol              |
|  2 | the-nightmare-before-christmas |
|  2 | chuck-jones                    |
+----+--------------------------------+

I want to get unique ids from the table where at least two of a list of slugs match for a given id.

For example lets say I've got the slugs values of:

I would want all unique ids that have a matching record for at least 2 of those.

i.e. only an id that had both the gift AND christmas slug or the giving AND christmas slug or the gift AND giving slug, etc...

Upvotes: 1

Views: 1679

Answers (1)

Mureinik
Mureinik

Reputation: 311063

You can use the distinct modifier to count the number of different slugs per ID:

SELECT   id
FROM     mytable
WHERE    slug IN ('gift', 'christmass', 'giving')
GROUP BY id
HAVING   COUNT(DISTINCT slug) >= 2

Upvotes: 3

Related Questions