Complex SQL query, is a loop necessary?

Question

SQL

Task:

The database has a table with products offers (id INTEGER, name TEXT), a table with tags tags (id INTEGER, name TEXT), and a table linking products and tags

tags_offers (tag_id INTEGER, offers_id INTEGER, UNIQUE (tag_id, offers_id))

.
Print the id and names of all products that have all possible tags in this database.
Implementation - SQL Query

My SQL practice comes down to writing basic queries, so I don't know where to start. If it wasn't SQL, I would loop through the array of products, skipping products that don't have all the tags - it's very simple. But I have no idea how to implement this in SQL. Maybe I'm misinterpreting the task (fans of programming competitions need your help)?

Answer 1

what about olympiad programming?
you need to compare the number of unique id values ​​in tags and unique tag_id values ​​in tags_offers for each offers_id. if it matches, then the condition is met.
this task. not a question.

Answer 2

Find all offers for which there is no tag that would not be attached to the offer.
select o.* from offers o
where not exists (select 1 from tags t
where not exists (select 1 from tags_offers t_o
where t_o.tag_id = t.id and t_o.offer_id = o.id))

Answer 3

The simplest option is probably with a variable:

declare @tags_count int
select @tags_count=count(*)
from tags;

select *
from offers as o
where o.id in
(
  select t_o.offers_id
  from tags_offers as t_o
  group by t_o.offers_id
  having count(*)[email protected]_count
)
order by o.id;

If you really want to do without variables, you can get out, for example, like this:

select o.*
from offers as o
cross join 
(
select count(*) as tags_max_count
from tags
) as t
join 
(
  select t_o.offers_id, count(*) as tags_count
  from tags_offers as t_o
  group by t_o.offers_id
) as t_o
on o.id=t_o.offers_id
where t.tags_max_count=t_o.tags_count
order by o.id;