Sql Joins...looking For Help

[superhit]

[quote name='cherlapalli_jailer' timestamp='1350507992' post='1302646411']
add group by function

select , date,team1 , sum(score1)

group by date, team, score1
[/quote]
Group by use cheyyali, sure.
But vishayam yentante, 2nd table (goal) lo aggregate scores levu. Goal table lo ye player belonging to which team, ye nimisham lo and ye match lo score chesaadu sni vundi.
But, output mathram per match...ye team yenni goals chesindo raavaaali.
Ante output table ilaa vundaaali:
Matchid team1 scoreOfTeam1 team2 scoreOfTeam2
Semis. Eng. 2. Pol. 3

[lolliman]

SELECT mdate, team1,sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1, team2, sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game JOIN goal ON matchid = id group by mdate, team1, team2

[lolliman]

[quote name='superhit' timestamp='1350508465' post='1302646477']
Group by use cheyyali, sure.
But vishayam yentante, 2nd table (goal) lo aggregate scores levu. Goal table lo ye player belonging to which team, ye nimisham lo and ye match lo score chesaadu sni vundi.
But, output mathram per match...ye team yenni goals chesindo raavaaali.
Ante output table ilaa vundaaali:
Matchid team1 scoreOfTeam1 team2 scoreOfTeam2
Semis. Eng. 2. Pol. 3
[/quote]


piana ichindi work avuthundi choodu baaa

[lolliman]

@superhit: work avutundaaa??

[superhit]

[quote name='lolliman' timestamp='1350508752' post='1302646521']


piana ichindi work avuthundi choodu baaa
[/quote]

ippude vachchaa BAaa...As of now, it says incorrect result. Let me check again and again

[Guillaume]

asala akada Germany akada undh?

[superhit]

[quote name='Guillaume' timestamp='1350523210' post='1302647655']
asala akada Germany akada undh?
[/quote]

Vaadu samplega konni rows ichcaadu. But, actual ga manaku kanipinchakunda there are many rows and many countries, matches, etc Baa,.....

[lolliman]

[quote name='superhit' timestamp='1350522829' post='1302647642']

ippude vachchaa BAaa...As of now, it says incorrect result. Let me check again and again
[/quote]

once nuvvu submit chesinatharavataaa.... akkada ee result and correct result ane option vastundi kadaa.... adi click chesthe same vunnai values

[superhit]

[quote name='lolliman' timestamp='1350508716' post='1302646514']
SELECT mdate, team1,sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1, team2, sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game JOIN goal ON matchid = id group by mdate, team1, team2
[/quote]

Baa...super nuvvu....correct chooyisthondi result. But, vaadu thappantunnaaadu.

But, actually this is correct....I compared both in excel

Thank you

[padmasri123]

[quote name='lolliman' timestamp='1350508716' post='1302646514']
SELECT mdate, team1,sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1, team2, sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game JOIN goal ON matchid = id group by mdate, team1, team2
[/quote]

It will work

[superhit]

Q): [color=#161525][font=HelveticaNeue,][background=rgb(210, 229, 209)]Write the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw. This question# 5 in [/background][/font][/color][url="http://sqlzoo.net/wiki/JOIN_Quiz#quiz0"]http://sqlzoo.net/wiki/JOIN_Quiz#quiz0[/url]

Per the website answer is): SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND mdate IN (SELECT mdate FROM game WHERE team1 = 'POL' OR team2 = 'POL')AND teamid != 'POL'

Why can't this one work??? SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE ((stadium = 'National Stadium, Warsaw') AND (team1 = 'POL' OR team2 = 'POL') AND (teamid != 'POL'))

[padmasri123]

[quote name='superhit' timestamp='1350527735' post='1302647793']
Q): [color=#161525][font=HelveticaNeue,][background=rgb(210, 229, 209)]Write the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw. This question# 5 in [/background][/font][/color][url="http://sqlzoo.net/wiki/JOIN_Quiz#quiz0"]http://sqlzoo.net/wiki/JOIN_Quiz#quiz0[/url]

Per the website answer is): SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND mdate IN (SELECT mdate FROM game WHERE team1 = 'POL' OR team2 = 'POL')AND teamid != 'POL'

Why can't this one work??? SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE ((stadium = 'National Stadium, Warsaw') AND (team1 = 'POL' OR team2 = 'POL') AND (teamid != 'POL'))
[/quote]

It will work for this particular question..

[superhit]

[quote name='padmasri123' timestamp='1350528375' post='1302647813']

It will work for this particular question..
[/quote]

Antey both the queries should yield the same answer antaaavaaaa Baa?

Verify cheddaam ani [url="http://sqlzoo.net/wiki/The_JOIN_operation"]http://sqlzoo.net/wiki/The_JOIN_operation[/url] indulo (where we have tables/database) ee rendu queries nu execute chesaanu. But they have displayed output Baa

[padmasri123]

[quote name='superhit' timestamp='1350528514' post='1302647818']

Antey both the queries should yield the same answer antaaavaaaa Baa?

Verify cheddaam ani [url="http://sqlzoo.net/wiki/The_JOIN_operation"]http://sqlzoo.net/wi..._JOIN_operation[/url] indulo (where we have tables/database) ee rendu queries nu execute chesaanu. But they have displayed output Baa
[/quote]


Ekkada chudochu output....naku telisi ee rendu queries work avutadi....

[superhit]

[quote name='padmasri123' timestamp='1350529286' post='1302647846']



Ekkada chudochu output....naku telisi ee rendu queries work avutadi....
[/quote]
Nenu paina ichchina website lo ee Queries execute cgeyyachchu...then that site shows up the output there itself baa