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.

I would like it to be one query but can only figure out how to do it with two seperate selects. One is getting the home games from the last most recent 10 and the second the away games. — Daryl Leak, Jul 21 '14 at 19:53
I highly suggest you split your question into two. Leave the inner query question here and move your other question into another question. — Patrick Szalapski, Jul 21 '14 at 19:57

T McKeown · Accepted Answer · 2014-07-21T20:02:11.400

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

That works, except that #TopMatches becomes a literal temporary table (on disk), which he might not want. — Patrick Szalapski, Jul 21 '14 at 19:54
I would have suggested a table variable but I didn't know the structure. But yes a table var is prefered... will update answer pointing that out... — T McKeown, Jul 21 '14 at 19:58
Hi thanks, that gives me a temporary table however, executing it more than once gives me "There is already an object named '#TopMatches' in the database." Do I have to drop it once the select is complete? — Daryl Leak, Jul 21 '14 at 19:58
Thankyou for that guys first time using temp tables, is there a massive overhead using a temporary table as apposed to a table variable? — Daryl Leak, Jul 21 '14 at 20:07
here is a breakdown on that exact question: http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server — T McKeown, Jul 21 '14 at 20:09

score 1 · Answer 2 · answered Jul 21 '14 at 19:54

Something like this?

with MatchDetails as
(
    SELECT TOP 10 
    Div as CompetitionID
    , AwayGoals
    , HomeGoals
    , HalfTimeAwayGoals
    , HalfTimeHomeGoals
    , AwayTeamCorners
    , HomeTeamCorners
    , AwayTeamFouls
    , AwayTeamYellows
    , AwayTeamReds
  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 MatchDate DESC
)

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 MatchDetails
group by CompetitionID

I would make a couple of suggestions. First I would name your column MatchDate instead of Date. Date is a reserved word and in incredibly ambiguous. What if Date means CreatedDate, or some other date.

Second and more importantly is I would highly recommend you look into normalization. You don't need columns for AwayGoals and HomeGoals. This would be far better if it were another table named Goals and included the TeamID. Same with Corners, Fouls, Cards (notice cards is another chance to normalize), no need for two card tables, just a card and a card type along with playerID (or team if not tracking at the player level) would suffice.

Thankyou, I will look into normalisation. I recieved the data in this format and haven't had much time to normalise fully so I have a lot of work to do until it is fully relational. — Daryl Leak, Jul 21 '14 at 20:09

SQL - Temporary table and selecting rows instead of sets

2 Answers2