combining results of two select statements

Question

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, [tableB].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

Answer 1

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.

Answer 2

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

Answer 3

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.