Как написать запрос для подсчета количества конкретных записей в другой таблице без внешнего ключа?

Question

Ниже приведена схема моего БД Я хочу получить все результаты из таблицы Subscription для данного Subscription.ClientId с номером Assigned.ScreenId для каждого Subscription.RouteId для этого Subscription.ClientId. Я попробовал код ниже, я также пытался наладить отношения, но безуспешно, Как я могу это сделать в yii.

SELECT S. * , (

SELECT COUNT( C.ScreenId ) 
FROM (

SELECT B.ScreenId, A.RouteId, B.clientId
FROM Screens AS A
INNER JOIN Assigned AS B ON A.ScreenId = B.ScreenId
) AS C
WHERE S.RouteId = C.RouteId
) AS couNTER
FROM Subscription AS S
LIMIT 0 , 30

Вот ссылка на образец http://sqlfiddle.com/ [примечание: я удалил таблицу подписанных экранов]

Что я хочу в качестве вывода

RouteId ClientId NumScreen NumAds... (Count(assignedScreenid)for given route)
1        1                              2
2        1                              1
1        2                              1
2        2                              3

Answer 1

Пройдите нижеприведенный sql, и если результат не тот, к которому вы стремитесь, возможно, уточните результаты, которые вы ожидали.

-- SELECT subscription.SubscriptionId, client.clientId, assigned.ScreenId, route.RouteId
SELECT route.RouteId, client.clientId, COUNT(assigned.ScreenId)
FROM subscription
  LEFT JOIN client ON client.ClientId = subscription.ClientId
  LEFT JOIN assigned ON assigned.ClientId = client.ClientId
  LEFT JOIN route ON route.RouteId = subscription.RouteId
GROUP BY assigned.ScreenId

Результаты:

ROUTEID   CLIENTID   COUNT(ASSIGNED.SCREENID)
   2         1         4
   2         1         2
   2         1         6
   1         2         2