SQL query: where array is in array

Question

I've got sample data in database:

id (int)   name (varchar)           parts (varchar)
1          some_element             wheel, bearing, hinge, servo
2          another_element          bearing, servo, lift
3          third_element            motor, wire

I want to filter results by parts. For example: I'm typing wheel, servo - no results

I'm typing wheel, bearing, servo, hinge - returns some_element record

I'm typing bearing, servo, lift, wheel, bearing, hinge - it returns some_element and another_element

How to construct SQL query? Is there any other data type better for parts field?

Answer 1

Do some normalization so that you can write queries more easily and won't have such anomalies.

You'll need another structure, like:

The element table

+----+---------------+
| id | name          |
+----+---------------+
|  1 | some_element  |
+----+---------------+
|  2 | another_elem  |
+----+---------------+
|  3 | third_elem    |
+----+---------------+

The part table

+----+----------+
| id | name     |
+----+----------+
|  1 | wheel    |
+----+----------+
|  2 | bearing  |
+----+----------+
|  3 | hinge    |
+----+----------+
|  4 | servo    |
+----+----------+
 etc..

And another, such as element_parts to connect the other two by an m:n relation

+----+---------+---------+
| id | elem_id | part_id |
+----+----------+--------+
|  1 | 1       | 1       |
+----+---------+---------+
|  2 | 1       | 2       |
+----+---------+---------+
|  3 | 1       | 3       |
+----+---------+---------+
|  4 | 2       | 3       |
+----+---------+---------+
|  5 | 2       | 4       |
+----+---------+---------+
 etc..

And now you can write a query to, say, filter elements that contain (or need) wheel and servo (adapting this question's accepted answer):

select *
from element e
where 2 = (
    select count(distinct p.id)
    from element_parts ep
    inner join part p on p.id = ep.part_id
    where p.name in ('wheel', 'servo') 
    and ep.elem_id = e.id
);