MySQL query issue - matching multiple id's on a joined table

Question

I've got 3 tables:

activities, tools and as these share a n:m relation a table activities_tools.

Users on my website can select available tools on a filter. I'm now trying to get all activities, where the according tools are available.

So e.g. if I'm selecting tools with the id's 1 and 2 as available, I should be getting activities requiring either no tools, tool 1, tool 2 as well as activities that require both. Activities requiring other tools should be therefore excluded.

My query I have tried with so far looks like this:

SELECT activities.*, GROUP_CONCAT(tool) AS tools

FROM activities

LEFT JOIN activities_tools ON activities_tools.activity = activities.id

WHERE ISNULL(tool) OR tool IN (1,2)

GROUP BY activities.id

The (1,2) is the list of id's selected by the user.

The ISNULL part solves the problem of getting activities which have no tools applied. My issue now it though, that e.g. a tool having tools 1 AND 2 as required is getting returned when only one of them is available.

How can I check it against multiple values at once? I have tried fiddling with GROUP_CONCAT + HAVING + FIND_IN_SET and more.. but I can't come up with a solution.

Any help is appreciated.

"My issue now it though, that e.g. a tool having tools 1 AND 2 as required is getting returned." - I'm confused, earlier in your post it sounds like that's what you want to happen? — miknik, Dec 10 '17 at 00:47
@miknik Sorry, I forgot to complete the sentence there. It's edited. — Yami, Dec 10 '17 at 00:52
Are the tool ids all in the same column? How are they stored? What datatype is it? — miknik, Dec 10 '17 at 01:01
@miknik tool ids are INT type. They are the foreign keys in activities_tools to the tools table. The tool id there is also INT (primary key). — Yami, Dec 10 '17 at 01:03
Why dont you start with an array holding all the tool ids, then subtract the ones selected by the user and use `NOT IN (2, 3, 4,....)` instead? — miknik, Dec 10 '17 at 01:10

score 0 · Answer 1 · answered Dec 10 '17 at 01:12

I think you can use an extra NOT IN check:

SELECT activities.*, GROUP_CONCAT(tool) AS tools

FROM activities

LEFT JOIN activities_tools ON activities_tools.activity = activities.id

WHERE ISNULL(tool) OR (tool IN (1,2) AND activities.id NOT IN (SELECT activity FROM activities_tools WHERE tool NOT IN (1,2)))

GROUP BY activities.id

Yami · Answer 2 · 2017-12-10T17:39:44.287

After trying some more things out and considering the input from here I've found my solution :)

SELECT activities.*, GROUP_CONCAT(tool) AS tools
FROM activities
LEFT JOIN activities_tools ON activities_tools.activity = activities.id
GROUP BY activities.id
HAVING ISNULL(tool) OR FIND_IN_SET(1, tools) = 0 OR FIND_IN_SET(2, tools) = 0 etc.

The "OR FIND_IN_SET(1, tools) = 1" parts are the id's to exclude. This part of the query is generated dynamically in PHP.

EDIT:

After reading a super cool trick here:

MySQL find_in_set with multiple search string

I have adjusted my solution to this:

SELECT activities.*, GROUP_CONCAT(tool) AS tools
FROM activities
LEFT JOIN activities_tools ON activities_tools.activity = activities.id
GROUP BY activities.id
HAVING CONCAT(",", toys, ",") NOT REGEXP ",(x|y|z),"

Where x , y and z equal to the exclusion id's. That way the query is way shorter.

MySQL query issue - matching multiple id's on a joined table

2 Answers2