molec

Самый простой вариант, наверное, с переменной:

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(*)=@tags_count
)
order by o.id;

Если очень хочется обойтись без переменных, можно выкрутиться, например, вот так:

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;