join table problem - need help !!!

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources