sqlite select when values are stored in an array

derrida
derrida used Ask the Experts™
on
hi
i have a table quotes that have a field quote_category which hold categories ids. meaning, this quote might belong to several categories.
i attach an image of the table.
for example: how can i select only quotes that the id 3 is in the quote_category array?
the database
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
not to provide a solution at this stage, but is that possible to change your table design? Personally thought is that by putting all the relevant ids into a simple column is really a bad design.

Do you think can create a separate table to hold the ids instead?

Author

Commented:
hi
can you elaborate on that idea? maybe example?
One of the major points of relational databases is to avoid (or eliminate) exactly that kind of operation. So, you're now trying to use a relational language (SQL) to do one of the things that it was designed not to do. If you think about that, it should become clear why it's not easy.

The idea is that you'd create a "related" table that lists the quote_categorys for each quote_id and/or quote_author (whatever the "relation" is). You wouldn't try to put multiple values into a single column.

Once that's done, it becomes simple, as well as easier to modify for future needs.

There possibly are a couple ways to get it done with what you have. It's hard to be sure for a couple reasons. For example, are the values in quote_category supposed to be numeric? (They aren't; they're substrings of character values, possibly of varchar values.) Are they always single-character values? (Or might they be larger after a few years go by so that you might have "(1,5,10,12)" in that column someday? Hard to be certain about the future.)
Software Team Lead
Commented:
so your original table is shown as follows:

Table0:
quote_id
quote_author
quote_category
quote_text

to derive to become 2 tables, for example:

Table0:
quote_id
quote_author
quote_text

Table0
Table1:
id (PK)
quote_id
quote_category

Table1
and then we join them when necessary to get the results we wanted

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial