Question

How to retrieve 'Player Name and Team Name'  teams which have more than one goals in the tournament?

Suppose I have 3 tables Goals, Player and Team.

Goals Table contain TEAM ID , GAME ID , FOR_TEAM ID. ( Here , FOR_TEAM ID is id of opposition team)

Player Table contain TEAM ID, PLAYER NAME and Player ID

Team Table contain TEAM ID, TEAM NAME

I need working SQL QUERY

Answer 1

select PLAYERNAME, TEAMNAME from Player

select PLAYERNAME, TEAMNAME from Player
inner join Team on Team.TEAM_ID = Player.PLAYER_ID
onne join Goals on Goals.TEAM_ID = Player.PLAYER_ID
group by PLAYERNAME
having count(Goals.FOR_TEAMID) > 1;

select PLAYERNAME, TEAMNAME from Player, Team, Goals
where Team.TEAM_ID = Player.PLAYER_ID and
Goals.TEAM_ID = Player.PLAYER_ID
group by PLAYERNAME
having count(Goals.FOR_TEAMID) > 1;

both queries can be used.

having -> display the selected rows with the condition

inner join - > join the rows with more than one table