Btukfyl

I have two tables:
Table _models with fields name and model_id
And
Table _tags with fields tag_name, tags_id and model_id

In my web app, I can assign some tags to a model by adding records in table _tags with the model’s model_id related field.

How can I SELECT from table _models just the models which have the same tags assigned in the _tags table?

For example, I need to SELECT all the models that have assigned both the tag #jacket and the tag #trench

For your example, you can use a GROUP BY/HAVING along with a COUNT DISTINCT to find models that have both of the tags assigned.

SELECT m.model_id, m.name

    FROM models m

        INNER JOIN tags t

            ON m.model_id = t.model_id

    WHERE t.tag_name IN ('#jacket', '#trench')

    GROUP BY m.model_id, m.name

    HAVING COUNT(DISTINCT t.tag_name) = 2;

Slightly different way to do the same:

SELECT

      m.model_id

    , m.name

FROM

    _models m

WHERE

    2 = (

        SELECT

            COUNT(*)

        FROM

            _tags t

        WHERE

                m.model_id = t.model_id

            AND t.tag_name IN ('#jacket', '#trench')

    )

Note 1: You would better move tag names to separate table. So 3 tables: models (id, name), tags (id, name), tags2models (tag_id, model_id)

Note 2: Do not forget to add index (tag_name, model_id) on table _tags