declare @Priority table (PID int, PName varchar(10)) insert into @Priority
      select 1, 'High' union
      select 2, 'Med' union
      select 3, 'Low' union
      select 4, 'Unknown'

declare @Status table (SID int, SName varchar(10)) insert into @Status
      select 1, 'Sent' union
      select 2, 'Deleted' union
      select 3, 'Inbox'

declare @Message table (MID int, PID INT) insert into @Message
      select 1, 2 union
      select 2, 2 union
      select 3, 1 union
      select 4, 2 union
	  select 5, 4 union 
	  select 6, 3

declare @MessageTo table (MTID int, MID int, SID INT, UID INT) insert into @MessageTo
      select 1, 1, 1, 1 union
      select 2, 4, 1, 1 union
      select 3, 2, 2, 2 union
      select 4, 2, 3, 1 union
	  select 5, 5, 1, 1 union
	  select 6, 6, 2, 1

declare @User table (UID int, UName varchar(10)) insert into @User
      select 1, 'Nathan' union
      select 2, 'Sam' union
      select 3, 'Jack'

declare @UID int
select	@UID = 1

select	s.SName, 
		p.PName, 
		sum(case when dd.UID is null then 0 else 1 end) cnt
from	(	select	s.SID, 
					p.PID
			from	@Status s
			cross
			join	@Priority p
		)d
left
join	(	select	m.MID, m.PID, mt.SID, mt.UID
			from	@Message m
			inner 
			join	@MessageTo mt on	
					m.MID = mt.MID
			where	mt.UID = @UID
		)dd on
		d.SID = dd.SID and
		d.PID = dd.PID
inner
join	@Priority p on
		d.PID = p.PID
inner
join	@Status s on
		d.SID = s.SID
group
by		s.SName, p.PName, dd.UID
order
by		s.SName, p.PName

-- desired:
SName      PName      cnt
---------- ---------- -----------
Deleted    High       0
Deleted    Low        1
Deleted    Med        0
Deleted    Unknown    0
Inbox      High       0
Inbox      Low        0
Inbox      Med        1
Inbox      Unknown    0
Sent       High       0
Sent       Low        0
Sent       Med        2
Sent       Unknown    1

select	d.SName, 
		d.PName, 
		sum(case when mt.UID is null then 0 else 1 end) cnt
from	(	select	s.SID, p.PID, s.SName, p.PName
			from	@Status s
			cross
			join	@Priority p
		) d
left
join	@Message m on d.PID = m.PID
left   
join	@MessageTo mt on m.MID = mt.MID and d.SID = mt.SID and mt.UID = @UID
group
by		d.SName, d.PName
order
by		d.SName, d.PName

select	s.SName, 
		p.PName, 
		sum(case when mt.UID is null then 0 else 1 end) cnt
from	@Status s
cross
join	@Priority p
left
join	@Message m on p.PID = m.PID
left   
join	@MessageTo mt on m.MID = mt.MID and s.SID = mt.SID and mt.UID = @UID
group
by		s.SName, p.PName
order
by		s.SName, p.PName