SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 join table problem - need help !!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lukael82
Starting Member

Slovenia
5 Posts

Posted - 12/03/2013 :  16:31:39  Show Profile  Reply with Quote
Hello,

I hope this is right forum to ask question. My problem is probably not so hard, but I can't resolve it - I'm a beginner in SQL.

I have 3 tables, first is:

REŽISER

ŠIFRA_REŽISERJA | PRIIMEK_REŽISERJA
1...................... | ALLEN
2...................... | HITCHCOCK
3, etc................ | KUBRICK, etc.

FILM

ŠIFRA_FILMA | ŠIFRA_REŽISERJA
1................ | 1
2................ | 5
3, etc.......... | 5, etc.


KASETA

ŠIFRA_FILMA | ŠTEVILO_IZPOSOJ
1................ | 4
2................ | 1
3, etc.......... | 2, etc

These three tables represent database of movies, their producing directors, and borrowed video-tape of their movies.

QUESTION : I need to create code, where I must show result of ŠIFRA_IGRALCA,PRIIMEK_IGRALCA and the sum of ŠTEVILO_IZPOSOJ where ŠIFRA_REŽISERJA is in ŠIFRA_FILMA, in DESC order.

MY CODE SO FAR :

select režiser.šifra_režiserja, režiser.priimek_režiserja, kaseta.število_izposoj
from REŽISER , FILM, KASETA
where REŽISER.ŠIFRA_REŽISERJA = FILM.ŠIFRA_REŽISERJA and FILM.ŠIFRA_FILMA = KASETA.ŠIFRA_FILMA
group by režiser.šifra_režiserja, režiser.priimek_režiserja, kaseta.število_izposoj
having ??????????????????
order by ŠTEVILO_IZPOSOJ desc;


As you see, I have joined tables but no calculations, problem lies in third table KASETA, where I need to calculate "distinct" ŠIFRA_REŽISERJA from ŠIFRA_FILMA, and give that value a SUM of ŠTEVILO_IZPOSOJ. I think I can resolve this in Having clause, but I don't know how

Hope that language isn't a problem, but for better understanding - all "ŠIFRA" names are basically "ID_NUMBER"

Thanks for all your help,

regards, Luka



Edited by - lukael82 on 12/03/2013 16:35:55

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/03/2013 :  16:58:40  Show Profile  Reply with Quote

select r.šifra_režiserja
       , r.priimek_režiserja
       , sum(k.število_izposoj) as Total_število_izposoj
from REŽISER r
inner join FILM f on f.ŠIFRA_REŽISERJA = r.ŠIFRA_REŽISERJA
inner join KASETA k on  k.ŠIFRA_FILMA = f.ŠIFRA_FILMA
group by r.šifra_režiserja
       , r.priimek_režiserja
order by sum(k.število_izposoj) desc;


Be One with the Optimizer
TG

Edited by - TG on 12/03/2013 16:59:54
Go to Top of Page

lukael82
Starting Member

Slovenia
5 Posts

Posted - 12/04/2013 :  13:06:22  Show Profile  Reply with Quote
It works, perfectly !! Thanks TG, You're the man
quote:
Originally posted by TG


select r.šifra_režiserja
       , r.priimek_režiserja
       , sum(k.število_izposoj) as Total_število_izposoj
from REŽISER r
inner join FILM f on f.ŠIFRA_REŽISERJA = r.ŠIFRA_REŽISERJA
inner join KASETA k on  k.ŠIFRA_FILMA = f.ŠIFRA_FILMA
group by r.šifra_režiserja
       , r.priimek_režiserja
order by sum(k.število_izposoj) desc;


Be One with the Optimizer
TG

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/04/2013 :  13:10:50  Show Profile  Reply with Quote
Ha. Nope just a man.
you're welcome.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000