Results 1 to 3 of 3

Thread: Is it possible

  1. #1
    Join Date
    Sep 2007
    Posts
    16

    Unanswered: Is it possible

    Got an database bo_Match,bo_MatchResult, bo_Club.

    So far i have an SELECT like:
    Code:
    SELECT DISTINCT bo_Club.ClubName,
    SUM(bo_MatchResult.MatchResultRoundResult1) AS Ser1, SUM(bo_MatchResult.MatchResultRoundResult2) AS Ser2,
    SUM(bo_MatchResult.MatchResultRoundResult3) AS Ser3,  SUM(bo_MatchResult.MatchResultRoundResult4) AS Ser4,
    SUM(bo_MatchResult.MatchResultRankPoint1 + bo_MatchResult.MatchResultRankPoint2 + 
    bo_MatchResult.MatchResultRankPoint3 + bo_MatchResult.MatchResultRankPoint4)/2 AS Banp, MatchResultHomeOrAwayTeam
    FROM bo_Match INNER JOIN
         bo_MatchResult ON bo_Match.MatchId = bo_MatchResult.MatchResultMatchId INNER JOIN
         bo_Club ON bo_MatchResult.MatchResultTeamId = bo_Club.ClubId
    GROUP BY bo_Club.ClubName, bo_Match.MatchId, bo_MatchResult.MatchResultHomeOrAwayTeam
    HAVING (bo_Match.MatchId = 2048075)
    And get following result :
    ClubName Ser1 Ser2 Ser3 Ser4 Banp MatchResultHomeOrAwayTeam
    --------- ----------- ----------- ----------- ----------- ----------- -------------------------
    BK Kaskad 1564 1583 1463 1571 7 B
    UBC 90 1517 1702 1571 1527 9 H

    (2 row(s) affected)
    My question is, can i compare HomeTeam against AwayTeam Result and get output 1 points for each MatchResultRoundResult?
    Regards
    Gert

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See the FAQ at the top of each page of the SQL Server forum for help getting us the information that we need (DDL and any additional DML) in order to answer your question.

    How can you tell the home team from the away team in your table?

    -PatP

  3. #3
    Join Date
    Sep 2007
    Posts
    16

    Here are the Table and Data Sorry!

    Put it in the attachments.
    Here are what i got so far:
    Code:
    SELECT DISTINCT c.ClubName AS ClumNameHome,
    SUM(mr.MatchResultRoundResult1) AS Ser1, 
    SUM(mr.MatchResultRoundResult2) AS Ser2,
    SUM(mr.MatchResultRoundResult3) AS Ser3,  
    SUM(mr.MatchResultRoundResult4) AS Ser4,
    SUM(mr.MatchResultRankPoint1 + mr.MatchResultRankPoint2 +
    mr.MatchResultRankPoint3 + mr.MatchResultRankPoint4)/2 AS Banp,
    mr.MatchResultHomeOrAwayTeam AS HomeOrAway,
    
    (SELECT DISTINCT c1.ClubName 
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS ClubNameAway,
    
    (SELECT DISTINCT SUM(mr1.MatchResultRoundResult1)  
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS Ser1A,
    (SELECT DISTINCT SUM(mr1.MatchResultRoundResult2)  
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS Ser2A,
    (SELECT DISTINCT SUM(mr1.MatchResultRoundResult3)  
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS Ser3A,
    (SELECT DISTINCT SUM(mr1.MatchResultRoundResult4)  
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS Ser4A,
    
    (SELECT DISTINCT SUM(mr1.MatchResultRankPoint1 + mr1.MatchResultRankPoint2 + 
    mr1.MatchResultRankPoint3 + mr1.MatchResultRankPoint4)/2  
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS BanpA,
    
    (SELECT mr1.MatchResultHomeOrAwayTeam  
    FROM bo_Match m1 INNER JOIN
         bo_MatchResult mr1 ON m1.MatchId = mr1.MatchResultMatchId INNER JOIN
         bo_Club c1 ON mr1.MatchResultTeamId = c1.ClubId
    WHERE (m1.MatchId = 2048075 AND mr1.MatchResultHomeOrAwayTeam ='B')
    GROUP BY c1.ClubName, m1.MatchId, mr1.MatchResultHomeOrAwayTeam) AS HomeOrAway
    FROM bo_Match m INNER JOIN
         bo_MatchResult mr ON m.MatchId = mr.MatchResultMatchId INNER JOIN
         bo_Club c ON mr.MatchResultTeamId = c.ClubId
    GROUP BY c.ClubName, m.MatchId, mr.MatchResultHomeOrAwayTeam
    HAVING (m.MatchId = 2048075 AND mr.MatchResultHomeOrAwayTeam ='H')
    Gerten
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •