Как правильно использовать JOIN для выборки из нескольких таблиц данные + COUNT(*)?
SELECT `b`.`id`, `b`.`title`, `b`.`description`, `b`.`public`, 
  (SELECT COUNT(*) FROM `likes` WHERE `boycottid` = :boycot_id) AS `likes`,
  (SELECT COUNT(*) FROM `dislikes` WHERE `boycottid` = :boycot_id) AS `dislikes`,
  (SELECT COUNT(*) FROM `subscriptions` WHERE `boycottid` = :boycot_id) AS `subscriptions`
  FROM (
    SELECT `id`, `title`, `description`, `public` FROM `boycotts` WHERE `id` = :boycot_id
  ) AS `b`
Но это только если нужно для конкретного id.
SELECT `b`.`id`, `b`.`title`, `b`.`description`, `b`.`public`, 
  (SELECT COUNT(*) FROM `likes` WHERE `boycottid` = :boycot_id) AS `likes`,
  (SELECT COUNT(*) FROM `dislikes` WHERE `boycottid` = :boycot_id) AS `dislikes`,
  (SELECT COUNT(*) FROM `subscriptions` WHERE `boycottid` = :boycot_id) AS `subscriptions`
  FROM (
    SELECT `id`, `title`, `description`, `public` FROM `boycotts` WHERE `id` = :boycot_id
  ) AS `b`
Но это только если нужно для конкретного id.