CODEWITHSUNDEEP
mysqlsql
joshua
joshua

Reputation: 4198

MYSQL subset operation

Is there a way to achieve something like:

SELECT *
FROM tb_values
WHERE (value1, value2, value3) SUBSET_OF
    ( SELECT value
      FROM tb_value
      WHERE isgoodvalue = true
    )

More information: I have a table called projects. Each project has tags. A tag can be shared by multiple projects. There is a mapping table called projectTagMap. Now users use tags to filter the projects. Tags are selected using checkboxes on the UI. So a user selects several tags to filter the projects. Am supposed to select the projects from projects table that contains all the tags selected by the user.

Upvotes: 4

Views: 6671

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

From your pseudo code I guess that you want to check if a (dynamic) list of values is a subset of another list provided by a SELECT. If yes, then a whole table will be shown. If not, no rows will be shown.

Here's how to achieve that:

SELECT *
FROM tb_values
WHERE 
    ( SELECT COUNT(DISTINCT value)
      FROM tb_value
      WHERE isgoodvalue = true
        AND value IN (value1, value2, value3)
    ) = 3

UPDATED after OP's explanation:

SELECT *
FROM project
  JOIN 
    ( SELECT projectid
      FROM projectTagMap
      WHERE isgoodvalue = true
        AND tag IN (tag1, tag2, tag3)
      GROUP BY projectid
      HAVING COUNT(*) = 3
    ) AS ok
    ON ok.projectid = project.id

Upvotes: 6

Mick Hansen
Mick Hansen

Reputation: 2694

Probably a primitive method but i suppose you could do:

WHERE value1 IN (SELECT value FROM tb_value WHERE isgoodvalue = true) OR value2 IN (...) ...

Upvotes: 0

Related Questions