How to combine the results of two sql queries?

Question

SQL

There are two databases, you need to execute query1 to DB1 , then substitute the received data into query2 to DB2 and display the result of the query in a pivot table. It is necessary to implement all this using VBA in Excel. I learned how to fill the pivot table with data, but I need help with the rest.

Answer 1

www.planetaexcel.ru/forum

Answer 2

You can use UNION
This will return query results in separate rows.
First you have to make sure that both queries return the same 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 already mentioned, both queries return completely different data. You probably only want to do this if both queries return data that could be treated similarly.
SO
You can use Join
if there is any data that is shared between two queries. This will put the results of both queries on the same line joined by id, which is probably more what you want to do here...
You can 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

One Two Three Four more...

Answer 4

Via jQuery