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

User · Answer

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.

User · Answer

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

User · Answer

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

[sql] combining results of two select statements

Examples related to sql

Examples related to tsql

Examples related to select