sql - joining two tables by value of one column and calculating -

thank interested in question. have in 1 table thing

sifrez | sifkorisnikpk | status   1    |    1          |  'p'   2    |    1          |  'p'   3    |    1          |  'u'   4    |    2          |  'p'   5    |    2          |  'p'   6    |    2          |  'u'   7    |    2          |  'u'   8    |    3          |  'u'   9    |    3          |  'u'   10   |    3          |  'u'   11   |    3          |  'u'   12   |    4          |  'p'   13   |    4          |  'p' 

then created stored function count values of p , u search ratio between them find user id , return username

create procedure sp_getbestusernames begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;      -- insert statements procedure here      select sifkorisnikpk, count (status) brojlosih     #losarez     rezervacija      status = 'p'     group sifkorisnikpk     order count(*) desc 

after part give me

sifkorisnikpk | brojlosih        1      |    2        2      |    2        4      |    2 

this count p on status. , this:

select sifkorisnikpk, count (status) brojdobrih #dobrarez rezervacija  status = 'u' group sifkorisnikpk order count(*) desc 

i get

sifkorisnikpk | brojdobrih      1        |    1      2        |    2      3        |    4 

only count u. part work charm... , here try join 2 temp tables , calculate ratio , return sifkorisnik best ratio

select username korisnik        sifkorisnik = (select top 1 a.sifkorisnikpk                           #dobrarez                             inner join #losarez b                            on a.sifkorisnikpk = b.sifkorisnikpk or                           a.sifkorisnikpk != b.sifkorisnikpk                               a.brojdobrih - b.brojlosih > 0                             order a.brojdobrih - b.brojlosih )      end 

and after joining operation expect thing this, i'm not getting

sifkorisnikpk | brojdobrih | brojlosih 1 | 1 | 2 2 | 2 | 2 3 | 4 | 0 4 | 0 | 2

so can calculate ratio brojdobrih - brojlosih , return sifkorisnikpk best ratio. in case sifkorisnik 3. procedure sifkorisnik 2 second best. presume problem in joining operation doesn't input 0. how can solve this?

try this:

select     a.sifkorisnikpk,    isnull(brojdobrih,0) brojdobrih,    isnull(brojlosih,0) brojlosih (select distinct sifkorisnikpk rezervacija) left join #losarez b    on a.sifkorisnikpk = b.sifkorisnikpk  left join #dobrarez c    on a.sifkorisnikpk  = c.sifkorisnikpk  order (isnull(brojdobrih,0) - isnull(brojlosih,0))