Mysql – Returning data where all categories exactly match

MySQL

I have a documents table that I'm rendering out in a ui.

This ui has a set of select2 dropdowns for different category types and the dropdowns are populated data from a categories table.

Each of these dropdowns allows for multiple selections and will post a named array of values to the server.

What I'm hoping to achieve is to only display the relevant documents based on matching the posted cat_id's within a cat_type

These are example tables

Documents

+----+------------+-------------+
| id | doc_title  | doc_version |
+----+------------+-------------+
|  1 | item 1     |           4 |
|  2 | document 2 |           3 |
+----+------------+-------------+

Categories

+----+--------+----------+
| id | cat_id | cat_type |
+----+--------+----------+
|  1 |      1 |        3 |
|  1 |      7 |        2 |
|  1 |      4 |        2 |
|  2 |      7 |        2 |
+----+--------+----------+

An example might be querying documents where the cat_id = 7 and the cat_type = 2.
This should to return row 1 and 2 of the documents table.

What I'm having difficulty with is query documents for a match where multiple cat_id's are declared for a cat_type.

An example might be querying documents where the cat_id = 7 and 4 and the cat_type = 2.
This should to return row 1 of the documents table.

I was thinking I could of used WHERE cat_id IN (4,7) AND cat_type = 2, however that returns results that match either cat_id.

How should I go about querying the data in order to return document entries that exactly match cat_id's for a cat_type?

Thanks

Best Answer

You cqan use the IN clause for that

SELECT 
`id`, `doc_title`, `doc_version` 
FROM Documents WHERE id in (SELECT `id` 
                            FROM Categories 
                            WHERE `cat_id` = 7  AND  `cat_type` = 2);

CREATE TABLE Documents (
  `id` INTEGER,
  `doc_title` VARCHAR(10),
  `doc_version` INTEGER
);

INSERT INTO Documents
  (`id`, `doc_title`, `doc_version`)
VALUES
  ('1', 'item 1', '4'),
  ('2', 'document 2', '3');

CREATE TABLE Categories (
  `id` INTEGER,
  `cat_id` INTEGER,
  `cat_type` INTEGER
);

INSERT INTO Categories
  (`id`, `cat_id`, `cat_type`)
VALUES
  ('1', '1', '3'),
  ('1', '7', '2'),
  ('1', '4', '2'),
  ('2', '7', '2');

✓

✓

✓

✓

SELECT 
`id`, `doc_title`, `doc_version` 
FROM Documents WHERE id in (SELECT `id` 
                            FROM Categories 
                            WHERE `cat_id` = 7  AND  `cat_type` = 2);

id | doc_title  | doc_version
-: | :--------- | ----------:
 1 | item 1     |           4
 2 | document 2 |           3

db<>fiddle here

Related Question