You need to
left outer join
your CTE results to the Retail table something like this
select a.*, ISNULL(b.TicketsSold,0) as RetailTickets
from cte a
left outer join retail b on a.Cinema = b.Cinema
and (b.[Month] is null or b.[Month] = a.[Month])
Use Left Outer join because there is no cinema 4 in the retail table. Try doing this with the lowest tickets sold to demo that it still works
Incidentally, I would do the cte slightly differently
with cte as
(
select top 1 *
from @tickets
order by TicketsSold desc
)
(I can use the
order by
in the cte only because I used
top 1
)