query_as_join.sql

SELECT tags.*, taggings.count
FROM tags
JOIN (
SELECT
 taggings.tag_id,
 count(*) as count
FROM taggings
INNER JOIN companies ON companies.id = taggings.taggable_id
 AND taggings.taggable_type = 'Company'
 AND `companies`.`visible` = 1 
 AND `companies`.`closed` = 0
 AND `companies`.`registration` = 0
JOIN taggings companies_taggings_dutch_230
 ON companies_taggings_dutch_230.taggable_id = companies.id
 AND companies_taggings_dutch_230.taggable_type = 'Company'
 AND companies_taggings_dutch_230.tag_id = 47
GROUP BY taggings.tag_id
HAVING COUNT(*) > 0
) AS taggings ON taggings.tag_id = tags.id