Combine three queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine three queries

Author

Topic

tonzo1883
Starting Member

12 Posts

Posted - 2009-01-05 : 10:14:32

Hi,

I am trying to combine three different queries but am having problems. Each one runs fine on its own but I cannot figure out how to combine them so all the values are in the one query.

Here are the three queries:

Query One:


SELECT     dbo.PlayerGames.PlayerId, dbo.Season.SeasonID, COUNT(dbo.PlayerGames.PlayerId) AS Appearances
FROM         dbo.PlayerGames INNER JOIN
                      dbo.Games ON dbo.PlayerGames.GameId = dbo.Games.GameId INNER JOIN
                      dbo.Season ON dbo.Games.SeasonID = dbo.Season.SeasonID
GROUP BY dbo.PlayerGames.PlayerId, dbo.Season.SeasonID
UNION ALL
SELECT     PlayerID, SeasonID, SUM(Appearances) AS Appearances
FROM         dbo.ArchiveAppearances
GROUP BY PlayerID, SeasonID

Query Two:


SELECT     dbo.Players.PlayerID, dbo.Season.SeasonID, dbo.Assists.Assists
FROM         dbo.Season INNER JOIN
                      dbo.Games ON dbo.Season.SeasonID = dbo.Games.SeasonID INNER JOIN
                      dbo.Assists ON dbo.Games.GameId = dbo.Assists.GameID INNER JOIN
                      dbo.Players ON dbo.Assists.PlayerID = dbo.Players.PlayerID
UNION ALL
SELECT     Players_1.PlayerID, dbo.ArchiveAssists.SeasonID, dbo.ArchiveAssists.Assists
FROM         dbo.ArchiveAssists INNER JOIN
                      dbo.Players AS Players_1 ON dbo.ArchiveAssists.PlayerID = Players_1.PlayerID

Query Three:


SELECT     dbo.Players.PlayerID, dbo.Season.SeasonID, dbo.Goals.Goals
FROM         dbo.Players INNER JOIN
                      dbo.Goals ON dbo.Players.PlayerID = dbo.Goals.PlayerID INNER JOIN
                      dbo.Games ON dbo.Goals.GameID = dbo.Games.GameId INNER JOIN
                      dbo.Season ON dbo.Games.SeasonID = dbo.Season.SeasonID
UNION ALL
SELECT     Players_1.PlayerID, dbo.ArchiveGoals.SeasonID, dbo.ArchiveGoals.Goals
FROM         dbo.ArchiveGoals INNER JOIN
                      dbo.Players AS Players_1 ON dbo.ArchiveGoals.PlayerID = Players_1.PlayerID

I want a query that returns the following columns:

PlayerID, SeasonID, Appearances, Goals, Assists

Any help would be very much appreciated!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:20:48

[code]
SELECT COALESCE(q1.PlayerID,q2.PlayerID,q3.PlayerID) AS PlayerID,
COALESCE(q1.SeasonID,q2.SeasonID,q3.SeasonID) AS SeasonID,
Appearances,
Assists,
Goals
FROM
(
SELECT dbo.PlayerGames.PlayerId, dbo.Season.SeasonID, COUNT(dbo.PlayerGames.PlayerId) AS Appearances
FROM dbo.PlayerGames INNER JOIN
dbo.Games ON dbo.PlayerGames.GameId = dbo.Games.GameId INNER JOIN
dbo.Season ON dbo.Games.SeasonID = dbo.Season.SeasonID
GROUP BY dbo.PlayerGames.PlayerId, dbo.Season.SeasonID
UNION ALL
SELECT PlayerID, SeasonID, SUM(Appearances) AS Appearances
FROM dbo.ArchiveAppearances
GROUP BY PlayerID, SeasonID
)q1
FULL OUTER JOIN
(
SELECT dbo.Players.PlayerID, dbo.Season.SeasonID, dbo.Assists.Assists
FROM dbo.Season INNER JOIN
dbo.Games ON dbo.Season.SeasonID = dbo.Games.SeasonID INNER JOIN
dbo.Assists ON dbo.Games.GameId = dbo.Assists.GameID INNER JOIN
dbo.Players ON dbo.Assists.PlayerID = dbo.Players.PlayerID
UNION ALL
SELECT Players_1.PlayerID, dbo.ArchiveAssists.SeasonID, dbo.ArchiveAssists.Assists
FROM dbo.ArchiveAssists INNER JOIN
dbo.Players AS Players_1 ON dbo.ArchiveAssists.PlayerID = Players_1.PlayerID
)q2
ON q2.PlayerId=q1.PlayerId
AND q2.SeasonID=q1.SeasonID
FULL OUTER JOIN
(
SELECT dbo.Players.PlayerID, dbo.Season.SeasonID, dbo.Goals.Goals
FROM dbo.Players INNER JOIN
dbo.Goals ON dbo.Players.PlayerID = dbo.Goals.PlayerID INNER JOIN
dbo.Games ON dbo.Goals.GameID = dbo.Games.GameId INNER JOIN
dbo.Season ON dbo.Games.SeasonID = dbo.Season.SeasonID
UNION ALL
SELECT Players_1.PlayerID, dbo.ArchiveGoals.SeasonID, dbo.ArchiveGoals.Goals
FROM dbo.ArchiveGoals INNER JOIN
dbo.Players AS Players_1 ON dbo.ArchiveGoals.PlayerID = Players_1.PlayerID
)q3
ON q3.PlayerId=q1.PlayerId
AND q3.SeasonID=q1.SeasonID
[/code]

tonzo1883
Starting Member

12 Posts

Posted - 2009-01-05 : 10:37:35

Thanks for that but it is not quite what I am after.

I want to be able use the query as a view and then use COUNT or SUM queries to give me the totals appearances, goals and assists for each player.

So for example if I run query one on a specific player I get total appearances of 2 but if I use the query you provided I get a total of 3.

I hope that makes sense!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:46:41

can you show some sample data to illustrate the discrepancy?

tonzo1883
Starting Member

12 Posts

Posted - 2009-01-05 : 12:01:27

For example if I run your query on playerID = 1 and SUM the appearances I get a total of 3

PlayerID SeasonID Appearances Assists Goals
----------- ----------- ----------- ----------- -----------
1 2 1 3 NULL
1 1 1 NULL 1
1 1 1 NULL 555

(3 row(s) affected)

But the original query one gives me a SUM of 2 appearances

PlayerId SeasonID Appearances
----------- ----------- -----------
1 1 1
1 2 1

(2 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:08:02

[code]
SELECT PlayerId, SeasonID,
SUM(Appearances) AS Appearances,
SUM(Assists) AS Assists,
SUM(Goals) AS Goals
FROM
(
SELECT dbo.PlayerGames.PlayerId, dbo.Season.SeasonID, COUNT(dbo.PlayerGames.PlayerId) AS Appearances,0 AS Assists,0 AS Goals
FROM dbo.PlayerGames INNER JOIN
dbo.Games ON dbo.PlayerGames.GameId = dbo.Games.GameId INNER JOIN
dbo.Season ON dbo.Games.SeasonID = dbo.Season.SeasonID
GROUP BY dbo.PlayerGames.PlayerId, dbo.Season.SeasonID
UNION ALL
SELECT PlayerID, SeasonID, SUM(Appearances) AS Appearances,0,0
FROM dbo.ArchiveAppearances
GROUP BY PlayerID, SeasonID

UNION ALL

SELECT dbo.Players.PlayerID, dbo.Season.SeasonID,0, dbo.Assists.Assists,0
FROM dbo.Season INNER JOIN
dbo.Games ON dbo.Season.SeasonID = dbo.Games.SeasonID INNER JOIN
dbo.Assists ON dbo.Games.GameId = dbo.Assists.GameID INNER JOIN
dbo.Players ON dbo.Assists.PlayerID = dbo.Players.PlayerID
UNION ALL
SELECT Players_1.PlayerID, dbo.ArchiveAssists.SeasonID,0, dbo.ArchiveAssists.Assists,0
FROM dbo.ArchiveAssists INNER JOIN
dbo.Players AS Players_1 ON dbo.ArchiveAssists.PlayerID = Players_1.PlayerID

UNION ALL

SELECT dbo.Players.PlayerID, dbo.Season.SeasonID, 0,0,dbo.Goals.Goals
FROM dbo.Players INNER JOIN
dbo.Goals ON dbo.Players.PlayerID = dbo.Goals.PlayerID INNER JOIN
dbo.Games ON dbo.Goals.GameID = dbo.Games.GameId INNER JOIN
dbo.Season ON dbo.Games.SeasonID = dbo.Season.SeasonID
UNION ALL
SELECT Players_1.PlayerID, dbo.ArchiveGoals.SeasonID,0,0, dbo.ArchiveGoals.Goals
FROM dbo.ArchiveGoals INNER JOIN
dbo.Players AS Players_1 ON dbo.ArchiveGoals.PlayerID = Players_1.PlayerID
)t
GROUP BY PlayerId, SeasonID
[/code]

tonzo1883
Starting Member

12 Posts

Posted - 2009-01-05 : 12:16:06

That's fantastic!

Thanks very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:20:55

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources