SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table gorevli (g_id int primary key, isim varchar (20),adres varchar(20), tel varchar(11));

create table liste (l_id int, g_id int references gorevli(g_id), tarih datetime);

insert into gorevli (g_id,isim,adres,tel) values
('1','ali','adana/ceyhan','11111111111'),
('2','ahmet','adana/merkez','22222222222'),
('3','mehmet','kayseri/melikgazi','33333333333'),
('4','huseyin','kayseri/yahyalı','44444444444'),
('5','mehmet','niğde/merkez','5555555555'),
('6','ali','konya/meram','66666666666');

insert into liste (l_id,g_id, tarih) values
('1',3, '20190101 10:00'),
('2',1, '20190101 10:00'),
('3',4, '20190101 10:00'),
('4',3, '20190101 11:00'),
('5',1, '20190101 11:00'),
('6',3, '20190101 12:00'),
('7',4, '20190101 10:00'),
('8',6, '20190101 10:00');

select g.g_id, g.isim, g.adres, g.tel, count(lst.g_id) as aranmaSayisi
from gorevli g
left join liste lst on lst.g_id = g.g_id
group by g.g_id, g.isim, g.adres, g.tel
order by count(lst.g_id) desc, g.isim;

select g.*, lst.tarih, 
case when lst.g_id is null then 'Hic aranmadi' else 'Arandi' end as durum
from gorevli g
left join liste lst on lst.g_id = g.g_id
order by g.isim, g.adres, lst.tarih;

select * 
from gorevli g
where not exists
(
  select * from liste lst 
  where lst.g_id = g.g_id
);

g_id	isim	adres	tel	aranmaSayisi
3	mehmet	kayseri/melikgazi	33333333333	3
1	ali	adana/ceyhan	11111111111	2
4	huseyin	kayseri/yahyali	44444444444	2
6	ali	konya/meram	66666666666	1
2	ahmet	adana/merkez	22222222222	0
5	mehmet	nigde/merkez	5555555555	0

g_id	isim	adres	tel	tarih	durum
2	ahmet	adana/merkez	22222222222	null	Hic aranmadi
1	ali	adana/ceyhan	11111111111	2019-01-01 10:00:00.000	Arandi
1	ali	adana/ceyhan	11111111111	2019-01-01 11:00:00.000	Arandi
6	ali	konya/meram	66666666666	2019-01-01 10:00:00.000	Arandi
4	huseyin	kayseri/yahyali	44444444444	2019-01-01 10:00:00.000	Arandi
4	huseyin	kayseri/yahyali	44444444444	2019-01-01 10:00:00.000	Arandi
3	mehmet	kayseri/melikgazi	33333333333	2019-01-01 10:00:00.000	Arandi
3	mehmet	kayseri/melikgazi	33333333333	2019-01-01 11:00:00.000	Arandi
3	mehmet	kayseri/melikgazi	33333333333	2019-01-01 12:00:00.000	Arandi
5	mehmet	nigde/merkez	5555555555	null	Hic aranmadi

g_id	isim	adres	tel
2	ahmet	adana/merkez	22222222222
5	mehmet	nigde/merkez	5555555555