Which will be the best query OR there is an another one?

Posted by serhan on Stack Overflow See other posts from Stack Overflow or by serhan
Published on 2010-05-21T12:43:44Z Indexed on 2010/05/21 12:50 UTC
Read the original article Hit count: 245

Filed under:
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

© Stack Overflow or respective owner

Related posts about sql