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!
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_
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
Thank you so much! Ive been sat at this for days now . 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.
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
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.
. 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.