SQL - Temporary table and selecting rows instead of sets

Question

I'm still learning SQL properly and have come across a little problem that I know can be solved I am just unsure how.

My query as it looks at the moment -

  SELECT 
  'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E' AS TeamId,
  Div AS CompetitionId,
  Count(*) AS GamesPlayed,
  Sum(HomeGoals) AS HomeGoals,
  Sum(AwayGoals) AS HomeGoalsConceded,
  Sum(HalfTimeHomeGoals) AS HalfTimeHomeGoals,
  Sum(HalfTimeAwayGoals) AS HalfTimeHomeGoalsConceded,
  Sum(HomeTeamCorners) AS HomeCorners,
  Sum(AwayTeamCorners) AS HomeCornersConceded,
  Sum(HomeTeamFouls) AS HomeFouls,
  Sum(HomeTeamYellows) AS HomeYellows,
  Sum(HomeTeamReds) AS HomeReds
  FROM
  (
      SELECT TOP 10 *
      FROM dbo.Match
      INNER JOIN dbo.MatchDetail ON dbo.Match.Id = dbo.MatchDetail.MatchId
      WHERE (HomeTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
        OR AwayTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E')
      ORDER BY [Date] DESC
  ) AS LatestMatches
 WHERE HomeTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
 GROUP BY Div

  SELECT 
  'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E' AS TeamId,
  Div AS CompetitionId,
  Count(*) AS GamesPlayed,
  Sum(AwayGoals) AS AwayGoals,
  Sum(HomeGoals) AS AwayGoalsConceded,
  Sum(HalfTimeAwayGoals) AS HalfTimeAwayGoals,
  Sum(HalfTimeHomeGoals) AS HalfTimeAwayGoalsConceded,
  Sum(AwayTeamCorners) AS AwayCorners,
  Sum(HomeTeamCorners) AS AwayCornersConceded,
  Sum(AwayTeamFouls) AS AwayFouls,
  Sum(AwayTeamYellows) AS AwayYellows,
  Sum(AwayTeamReds) AS AwayReds
  FROM
  (
      SELECT TOP 10 *
      FROM dbo.Match
      INNER JOIN dbo.MatchDetail ON dbo.Match.Id = dbo.MatchDetail.MatchId
      WHERE (HomeTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
        OR AwayTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E')
      ORDER BY [Date] DESC
  ) AS LatestMatches
 WHERE AwayTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
 GROUP BY Div

I understand this is very messy, firstly can I strip out the "SELECT TOP 10*" into a temporary table and how? Secondly to improve it further could I achieve this in a single query with one row rather than two rows/sets?

Thanks in advance will try explain more if needs be.

Answer 1

Add this to the beginning of your SQL:

SELECT TOP 10 *
  INTO #TopMatches
  FROM dbo.Match
  INNER JOIN dbo.MatchDetail ON dbo.Match.Id = dbo.MatchDetail.MatchId
  WHERE (HomeTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
    OR AwayTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E')
  ORDER BY [Date] DESC

Then use this temp table instead, so this:

FROM
(
  SELECT TOP 10 *
  FROM dbo.Match
  INNER JOIN dbo.MatchDetail ON dbo.Match.Id = dbo.MatchDetail.MatchId
  WHERE (HomeTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
    OR AwayTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E')
  ORDER BY [Date] DESC
) AS LatestMatches

Becomes this:

FROM #TopMatches AS LatestMatches

Alternatively, I would actually use a TABLE variable but that would require knowing the structure (you should post it in your question)

So ideally this is what you really want:

 DECLARE @TopMatches TABLE( COL1 AS INT, COL2 AS VARCHAR)   <-- fully define what columns you need to store

Now populate it using the query

 INSERT @TopMatches(COL1, COL2 .... ) SELECT TOP 10 *
  INTO #TopMatches
  FROM dbo.Match
  INNER JOIN dbo.MatchDetail ON dbo.Match.Id = dbo.MatchDetail.MatchId
  WHERE (HomeTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E'
    OR AwayTeamId = 'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E')
  ORDER BY [Date] DESC

If you want a SINGLE result set I would suggest you change your columns to be more generic, for example you have AwayGoals and HomeGoals instead you should ADD a column named Team and give it a value of Home or Away then you could do a UNION and get a single result:

A small example of what you could do:

SELECT 'Home' AS [Team],
'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E' AS TeamId,
 Div AS CompetitionId,
 Count(*) AS GamesPlayed,
 Sum(AwayGoals) AS Goals
FROM #TopMatches AS LatestMatches
WHERE ....   <-- whatever criteria for HOME
UNION ALL
SELECT 'Away' AS [Team],
'E3FBE4EC-B5AA-4558-A9A1-003D05D9A92E' AS TeamId,
 Div AS CompetitionId,
 Count(*) AS GamesPlayed,
 Sum(AwayGoals) AS Goals
FROM #TopMatches AS LatestMatches
WHERE ....   <-- whatever criteria for AWAY