Double results after join

[kmorrissey89]

Hello,

 

Sorry but I'm sure this is an easy question but I am pretty new to SQL and trying to learn this on my own. I recently tried doing a query and got the results I was looking for but when I went to join a third table to get some more information in there all of the results doubled. Here is the code that I have, before I joined the team table to get the teamname in there everything worked fine.

 

 

Select p.commonname + ' ' + p.lastname as battername, teamname, count(defenseshift) as Shift

from players as p

left join results as r on r.batterid = p.playerid

left join team as t on t.teamid = r.batteamid

where r.defenseshift = 7

group by p.commonname, p. lastname, t.teamname

order by shift desc

 

Like I said I'm sure this is a 101 kind of problem, but any help in describing my mistake would be appreciated.

 

Thanks

[justsomeguy]

That seems like it should work with the group by clause, when you add the team join each record appears twice in the result?

[kmorrissey89]

yeah the count for the shifts doubles when I did the second join

[justsomeguy]

Are the team names the same or different for one player on the results?

[kmorrissey89]

the team names are the same it is only the shift column that changes

[justsomeguy]

Try to group by battername instead of commonname and lastname.

[dauruk0512]

Yes i was experience about it, you should group by each field in table master when you join to another table if you don't it always give redudancy data !

 

Hope this make sense

 

 

Freddy