SQLTeam.com | Weblogs | Forums

HELP with ?joins


#1

Hi I am new to learning SQL and am stuck on a particular question on how to extract information from 2 tables. Im learning via FInnish language so sometimes I think the lessons are often lost in translation. This is the connections and the tables we are working from

The question is basically, I need to take the htun enim snimi from the henkilo table and I also need to show how many projects each individual person has done and the total number of hours they have worked (tunnit = hours worked). I am having problems trying to get it to show how many projects and total hours have been worked by each individual.
Any help is greatly appreciated!


#2

up to now Ive managed to get
SELECT henkilo.htun, COUNT(proj_henk.ptun) AS projlkm, SUM(tunnit)AS yht
FROM proj_henk LEFT JOIN henkilo ON proj_henk.htun=henkilo.htun
GROUP BY henkilo.htun
but cant get it to show me the enimi or snimi......or I can get it to show the enimi, snimi, htun but not count the projects or the hours worked. I know its probably something very simple Im doing wrong but I just cant see what it is! Ive been at it for hours :D_


#3

I'd turn the tables round, but only because I find it easier to visualise. I would also get rid of the LEFT JOIN and use an INNER JOIN - presumably for any [proj_henk] row there will always be a row in [henkilo]?

SELECT H.htun, COUNT(P.ptun) AS projlkm, SUM(tunnit)AS yht,
       H.enimi, H.snimi
FROM  henkilo AS H
    INNER JOIN proj_henk AS P
        ON P.htun=H.htun
GROUP BY H.htun,
       H.enimi, H.snimi

#4

Thank you so much! Ive been sat at this for days now :smiley: . Ive even done just as you have before but didnt use the inner join.....it was the only one I didn't try!. Now I shall sleep tonight without trying to solve this in my sleep.


#5

Hmmm ... LEFT JOIN should still work (the way round you had the tables), it just doesn't read as easily [to my eyes at least!] Plus it is an inner join (i.e. corresponding rows in [henkilo] will always exist), but SQL isn't going to care if you tell it that rows might NOT exist, by using a LEFT JOIN [although SQL may well make a less efficient query because if you use an INNER JOIN then SQL will optimise the query only for rows that DO exist - but in some/many cases it will make no difference to how SQL constructs the query]

P.S. You DO need to have those extra columns in the GROUP BY clause though


#6

The extra clauses is probably what I was doing wrong. I think I only tried doing it by the H.htun in my many attempts. Thank you for the help! greatly appreciate it.