Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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 !!!
 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
6065 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
6065 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  
 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.1 seconds. Powered By: Snitz Forums 2000