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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 join table problem - need help !!!

Author  Topic 

lukael82
Starting Member

5 Posts

Posted - 2013-12-03 : 16:31:39
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


TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-03 : 16:58:40
[code]
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;
[/code]

Be One with the Optimizer
TG
Go to Top of Page

lukael82
Starting Member

5 Posts

Posted - 2013-12-04 : 13:06:22
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 13:10:50
Ha. Nope just a man.
you're welcome.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -