combining results of two select statements
I'm using T-SQL with ASP.NET, and c# and i'm pretty new to SQL.
I was wondering how i could combine the results of two queries
Query1:
SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS NumberOfUsers
FROM tableD RIGHT OUTER JOIN
[tableB] INNER JOIN
tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id
GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tabl开发者_StackOverfloweB].CompanyImageUrl
Query2:
SELECT tableA.Id, tableA.Name, COUNT([tableC].Id) AS NumberOfPlans
FROM [tableC] RIGHT OUTER JOIN
tableA ON [tableC].tableAId = tableA.Id
GROUP BY tableA.Id, tableA.Name
Any help would be much appreciated. Thanks in advance
You can use a Union.
This will return the results of the queries in separate rows.
First you must make sure that both queries return identical columns.
Then you can do :
SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS Number
FROM tableD
RIGHT OUTER JOIN [tableB]
INNER JOIN tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id
GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl
UNION
SELECT tableA.Id, tableA.Name, '' AS Owner, '' AS ImageUrl, '' AS CompanyImageUrl, COUNT([tableC].Id) AS Number
FROM
[tableC]
RIGHT OUTER JOIN tableA ON [tableC].tableAId = tableA.Id GROUP BY tableA.Id, tableA.Name
As has been mentioned, both queries return quite different data. You would probably only want to do this if both queries return data that could be considered similar.
SO
You can use a Join
If there is some data that is shared between the two queries. This will put the results of both queries into a single row joined by the id, which is probably more what you want to be doing here...
You could do :
SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS NumberOfUsers, query2.NumberOfPlans
FROM tableD
RIGHT OUTER JOIN [tableB]
INNER JOIN tableA ON [tableB].Id = tableA.Owner ON tableD.tableAId = tableA.Id
INNER JOIN
(SELECT tableA.Id, COUNT([tableC].Id) AS NumberOfPlans
FROM [tableC]
RIGHT OUTER JOIN tableA ON [tableC].tableAId = tableA.Id
GROUP BY tableA.Id, tableA.Name) AS query2
ON query2.Id = tableA.Id
GROUP BY tableA.Name, [tableB].Username, [tableB].ImageUrl, [tableB].CompanyImageUrl
While it is possible to combine the results, I would advise against doing so.
You have two fundamentally different types of queries that return a different number of rows, a different number of columns and different types of data. It would be best to leave it as it is - two separate queries.
Probably you use Microsoft SQL Server which support Common Table Expressions (CTE) (see http://msdn.microsoft.com/en-us/library/ms190766.aspx) which are very friendly for query optimization. So I suggest you my favor construction:
WITH GetNumberOfPlans(Id,NumberOfPlans) AS (
SELECT tableA.Id, COUNT(tableC.Id)
FROM tableC
RIGHT OUTER JOIN tableA ON tableC.tableAId = tableA.Id
GROUP BY tableA.Id
),GetUserInformation(Id,Name,Owner,ImageUrl,
CompanyImageUrl,NumberOfUsers) AS (
SELECT tableA.Id, tableA.Name, tableB.Username AS Owner, tableB.ImageUrl,
tableB.CompanyImageUrl,COUNT(tableD.UserId),p.NumberOfPlans
FROM tableA
INNER JOIN tableB ON tableB.Id = tableA.Owner
RIGHT OUTER JOIN tableD ON tableD.tableAId = tableA.Id
GROUP BY tableA.Name, tableB.Username, tableB.ImageUrl, tableB.CompanyImageUrl
)
SELECT u.Id,u.Name,u.Owner,u.ImageUrl,u.CompanyImageUrl
,u.NumberOfUsers,p.NumberOfPlans
FROM GetUserInformation AS u
INNER JOIN GetNumberOfPlans AS p ON p.Id=u.Id
After some experiences with CTE you will be find very easy to write code using CTE and you will be happy with the performance.
精彩评论