1

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?

2
  • 6
    You need to normalize your data. Commented Jun 7, 2015 at 21:38
  • 2
    What RDBMS are you using? Commented Jun 7, 2015 at 21:43

1 Answer 1

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
);
Sign up to request clarification or add additional context in comments.

2 Comments

It's so easy! Thanks :)
Well, the "easier query writing" part was a bit of a lie, of course you can do some RegEx pattern matching magic (like LIKE), but that's just smothering up the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.