declare @Persons table(name varchar(10), id int)
insert into @Persons(name, id)
select 'Миша', 1 union all
select 'Ваня', 2 union all
select 'Катя', 3 union all
select 'Соня', 4 

declare @Comments table(ID int, _GroupID int, Comment varchar(20), _PersonID int)
insert into @Comments(ID, _GroupID, Comment, _PersonID)
select 1, 1, 'Звонил год назад', 1 union all
select 2, 2, 'Зачем-то звонил', 1 union all
select 3, 1, 'Звонок сегодня', 3 union all
select 4, 3, 'ЕщёИнформация', 4 union all
select 5, 6, 'Информация2', 2 

declare @Groups table(ID int, GroupID int, GroupOfNumbersID int, _Number varchar(12))
insert into @Groups(ID, GroupID, GroupOfNumbersID, _Number)
select 1, 1, 1, '3836 607-607' union all
select 2, 1, 1, '3836 744-477' union all
select 3, 1, 2, '4040 555-120' union all
select 4, 1, 1, '5511 123-123' union all
select 5, 2, 1, '4040 555-120' union all
select 6, 2, 1, '5511 123-123'

declare @Numbers table(Number varchar(12))
insert into @Numbers (Number)
select '3836 607-607' union all
select '3836 744-477' union all
select '4040 555-120' union all
select '5511 123-123' 

SELECT g._Number, c.Comment, p.name
FROM @Comments c RIGHT JOIN @Groups AS g ON c._GroupID = g.ID LEFT JOIN
@Persons AS p ON c._PersonID = p.ID 
WHERE g.GroupOfNumbersID=(select g.GroupOfNumbersID from @Groups as g where g._Number='5511 123-123' and g.GroupID=1) AND g.GroupID=1;

 _Number		Comment			name 
3836 607-607	Звонил год назад	Миша
3836 607-607	Звонок сегодня		Катя
3836 744-477	Зачем-то звонил		Миша
5511 123-123	NULL			NULL