1

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

1

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

  • Thanks - I think that worked. I think I can extend this for multiple cat_id's by adding additional and clauses - see here dbfiddle.uk/… – Matt Mar 11 '20 at 0:20
  • 1
    You can add more criteria to the where clause to get the right ids dbfiddle.uk/… your query excludes id 2. – nbk Mar 11 '20 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.