SELECT k.id,k.adsoyad, COUNT(DISTINCT(e.id)) as iletisimbilgisisay, COUNT(DISTINCT(f.id)) AS ilangonderensay,
COUNT(DISTINCT(g.id)) AS emlaksahibisay, isNULL(MAX(eb.yonetici_kisi),0) AS yoneticiid
FROM dbo.kisiler k
LEFT OUTER JOIN dbo.emlaklar e ON e.iletisimbilgisi=k.id
LEFT OUTER JOIN dbo.emlaklar f ON f.ilangonderen=k.id
LEFT OUTER JOIN dbo.emlaklar g ON g.emlaksahibi=k.id
LEFT OUTER JOIN dbo.emlakcibilgileri eb ON eb.yonetici_kisi=k.id
GROUP BY k.id,k.adsoyad
ORDER BY yoneticiid DESC, iletisimbilgisisay DESC, ilangonderensay DESC
total execution time (above) 28
SELECT id,adsoyad,
(select COUNT(id) FROM dbo.emlaklar WHERE iletisimbilgisi=k.id) AS iletisimbilgisisay,
(select COUNT(id) FROM dbo.emlaklar WHERE emlaksahibi=k.id) AS emlaksahibisay,
(select COUNT(id) FROM dbo.emlaklar WHERE ilangonderen=k.id) AS ilangonderensay,
(Select isNULL(MAX(id),0) FROM dbo.emlakcibilgileri WHERE yonetici_kisi=k.id) AS yoneticiid
FROM dbo.kisiler k
total execution time 4
my tables are
emlaklar: id int, ilangonderen int,iletisimbilgisi int,emlaksahibi int
kisiler: id int,kisiadi
emlakcibilgileri: id int,yonetici_kisi int,firma
and ilangonderen,iletisimbilgisi,emlaksahibi,yonetici_kisi => kisiler.id
