soccer table head-to-head query

[sgramesh75]

Hi Guys,
I have table called Match_Details and inserting records like goal,foul,coronr,red & yellow card
I need sum(goal) head to head query, for example if I send parameter (teamid 1 & 2) the query should return record only teamid 1 play with teamid 2

result: teamA TeamB
3 1

My Table structure
Tourid Match_Id teamid goal
1 1 1 1
1 1 2 1
1 1 1 1
1 2 1 1
1 2 3 1


1 3 1 1
1 3 2 0

[Micron]

Something like

SELECT Tourid, Match_Id, teamid FROM Tourid WHERE Tourid=1 AND Match_Id = 1 AND teamid = 1
UNION
SELECT Tourid, Match_Id, teamid FROM Tourid WHERE Tourid=1 AND Match_Id = 1 AND teamid = 2

The above assumes you care about the tour and match being the same for both teams, otherwise in subsequent years, you'll get records you don't want where the tourid is some other number but the team and match numbers are repeated. I have no idea how you want to provide those number to the query, so I just used #1 for example.

but you have two rows of 1,1,1,1 which doesn't seem right.

[sgramesh75]

Thanks for ur reply...but i am inserting each info during soccer match (like goal,red card,offside...) and each match_id has two teams, if i send parameters team_id 1 & 2 the result sum(goal) for both team should return only team_id 1 played with team_id 2 group by match_Id


I have another table called Match_Master and field structure
Match_Id, TeamA-Id,TeamB-Id)......
1 1 2
2 1 3
3 1 2