CODEWITHSUNDEEP
group-bymariadbmany-to-manyhavingintersect
Phantom
Phantom

Reputation: 688

MariaDB - Conjunction-Search in Many-to-Many

I have problems to implement an "and-concatenated" search with many-to-many tables. I tried to present a simple example below. I use MariaDB. I have a table with process. To the process a can assign persons and tags. There is a table for tags and a table for persons. There a two many-to-many relationships: tags_to_processes and persons_to_processes.

  1. example: Find all process with person 1 and person 2 and with tag 1 and 2. Result: process 1.

  2. example: Find all process with person 1 and person 2 and with tag 2. Result: Process 1 and Process 2.

Thank you very much!

'processes' Table
+-----------+-------------------+
|process_id |process_name       |
+-----------+-------------------+
|1          |Process 1          |
|2          |Process 2          |
|3          |Process 3          |
+-----------+-------------------+

'persons' table
+----------+------------+
|person_id |person_name |
+----------+------------+
|1         |Person 1    |
|2         |Person 2    |
|3         |Person 3    |
|4         |Person 4    |  
|5         |Person 5    |
+----------+------------+

'tags' table
+----------+-----------+
|tag_id    |tag_name   |
+----------+-----------+
|1         |Tag 1      |
|2         |Tag 2      |
|3         |Tag 3      |
|4         |Tag 4      |
|5         |Tag 5      |
|6         |Tag 6      |
+----------+-----------+

'persons_to_processes' table
+----------+-----------+
|person_id |process_id |
+----------+-----------+
|1         |1          |
|2         |1          |
|3         |1          |
|4         |1          |
|5         |1          |
|1         |2          |
|2         |2          |
|4         |3          |
+----------+-----------+

'tags_to_processes' table
+----------+-----------+
|tag_id    |process_id |
+----------+-----------+
|1         |1          |
|2         |1          |
|3         |1          |
|6         |1          |
|2         |2          |
|2         |3          |
+----------+-----------+

Upvotes: 0

Views: 63

Answers (1)

forpas
forpas

Reputation: 164099

You can join persons_to_processes to persons, filter the resuults for the persons that you want and use aggregation:

SELECT ptp.process_id
FROM persons_to_processes ptp INNER JOIN persons p
ON p.person_id = ptp.person_id
WHERE p.person_name IN ('Person 1', 'Person 2')
GROUP BY ptp.process_id
HAVING COUNT(*) = 2 -- 2 persons

Similarly for the tables tags_to_processes and tags:

SELECT ttp.process_id
FROM tags_to_processes ttp INNER JOIN tags t
ON t.tag_id = ttp.tag_id
WHERE t.tag_name IN ('Tag 1', 'Tag 2')
GROUP BY ttp.process_id
HAVING COUNT(*) = 2 -- 2 tags

Finally, you can combine the 2 queries to get their common results with INTERSECT:

 WITH 
  cte1 AS (
    SELECT ptp.process_id
    FROM persons_to_processes ptp INNER JOIN persons p
    ON p.person_id = ptp.person_id
    WHERE p.person_name IN ('Person 1', 'Person 2')
    GROUP BY ptp.process_id
    HAVING COUNT(*) = 2 -- 2 persons  
  ),
  cte2 AS (
    SELECT ttp.process_id
    FROM tags_to_processes ttp INNER JOIN tags t
    ON t.tag_id = ttp.tag_id
    WHERE t.tag_name IN ('Tag 1', 'Tag 2')
    GROUP BY ttp.process_id
    HAVING COUNT(*) = 2 -- 2 tags  
  )
SELECT process_id FROM cte1
INTERSECT
SELECT process_id FROM cte2;

See the demo.

Upvotes: 1

Related Questions