Combining Results of Two Select Statements

combining results of two select statements

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

JOIN two SELECT statement results

SELECT t1.ks, t1.[# Tasks], COALESCE(t2.[# Late], 0) AS [# Late]
FROM 
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks);

combine two select query result on some conditions

One way would be to use a cross apply (though a join would also work, it just gets a bit more complicated to write):

SELECT tbl1.EndDate, tbl1.PEL, tbl1.FL, C.CumEL, C.CumFL
FROM tbl1
CROSS APPLY 
(
    SELECT SUM(CumEL) AS CumEL, SUM(CumFL) AS CumFL
    FROM tbl2
    WHERE tbl2.EndDate <= tbl1.EndDate
) AS C;

Combining two SELECT statements into one result

One option, perhaps a bit brute force, would be to just use three separate subqueries in a single select:

SELECT
    (SELECT COUNT(*) FROM users WHERE email LIKE '%@rootix.de') AS postbox,
    (SELECT SUM(size) FROM users WHERE email LIKE '%@rootix.de') AS size,
    (SELECT COUNT(*) AS FROM aliases WHERE alias LIKE '%@rootix.de') AS aliases;

How to combine two select statements into one select statement in MSSQL?

Normally, you would use an inner join for things like that. But inner join needs some common columns between the two objects it joins, and you don't have that.

Since your queries also does not contain an ORDER BY clause, there is no reliable way to join them so that each row in table1 will always be joined to the same row in table2.

However, since both tables have a time column, you can use that:

;WITH CTE1 AS
(
    SELECT  a1, 
            b1, 
            c1,
            ROW_NUMBER() OVER(ORDER BY [time]) AS rn
    FROM Table1
    WHERE time between '2018-03-05' and '2018-03-06'
), CTE2 AS
(
    SELECT  a2, 
            b2, 
            c2,
            ROW_NUMBER() OVER(ORDER BY [time]) AS rn
    FROM Table2
    WHERE time between '2018-03-05' and '2018-03-06'
)

SELECT t1.a1, t1.b1, t1.c1, t2.a2, t2.b2, t2.c2                     
FROM cte1 as t1
INNER JOIN cte2 as t2 ON t1.rn = t2.rn

How to combine two select query with different columns

Consider your first query as Query1 and second query as Query2, you can use simple join between these two.

As you said PatientAppointment table is common in both, use its primary key(CDRId) as joining between these two. So your query would look like.

SELECT * 
FROM ( Query1 ) AS table1
INNER JOIN ( Query2) AS table2 ON table1.CDRId = table2.CDRId;

How to merge the result of two select mysql query

You can use either LEFT JOIN, RIGHT JOIN or JOIN depending on what you are aiming to get,

SELECT * 
FROM ( select GEAR,count(GEAR) 
       from new_failure 
       where STN_CODE = "BVH" group by(Gear) AS A
JOIN ( select GEAR,count(GEAR) 
       from new_failure 
       where STN_CODE = "BVH"  
       and MONTH(fail_time) = 4 AS B
ON A.orders_id=B.orders_id

or you can refer to this link for a similar question
joining two select statements

Merge two SQL SELECT queries results into one query and one result

Using UNION:

SELECT 
'#b= '+CAST(COUNT(b.b_id) AS VARCHAR)   b_COUNT,
'#a = '+CAST(COUNT(a.a_id) AS VARCHAR)  a_COUNT  
FROM #a as a LEFT JOIN #b as b    ON a.a_id = b.a_id AND a.is_active = 0
WHERE CONVERT(VARCHAR(10), a.cr_date, 111)   BETWEEN @STARTDATE AND @ENDDATE
UNION
SELECT 
'#d= '+CAST(COUNT(d.d_id) AS VARCHAR)   d_COUNT,
'#c = '+CAST(COUNT(c.c_id) AS VARCHAR)  c_COUNT  
FROM #c as c LEFT JOIN #d as d    ON c.c_id = d.c_id AND c.is_active = 0
WHERE CONVERT(VARCHAR(10), c.cr_date, 111)   BETWEEN @STARTDATE AND @ENDDATE

---

Related Topics