TimJohnson
TimJohnson

Reputation: 933

Combine different columns from 2 different queries into one resultset

There are many similar questions but not exactly what I wanted to I'm reserved to asking a question so forgive me if this is a duplicate as I couldn't find precisely what I wanted.

I have a query:

SELECT TOP 1 t1.Col1, t1.Col2, <need_to_append_here> 
FROM t1 
LEFT OUTER JOIN t2 
ON t1.Id = t2.Id 
WHERE t1.Id = 'x'
ORDER BY t2.Col2 DESC

Where you see need_to_append_here, I need a completely different sql to be appended to the result so I get 4 columns in the result set:

SELECT t3.Col3, t3.Col4 FROM t3 WHERE t3.Id = 'z'

I should see one row with Col1, Col2, Col3, Col4

UPDATE I was able to get it to work but with a single column from the 2nd query by doing something like

SELECT * FROM (SELECT TOP 1 t1.Col1, t2.Col2, (SELECT t3.Col3..) Col3
FROM ....

But I'm unable to include 2 columns in the 2nd select

Upvotes: 0

Views: 46

Answers (3)

Daniel Villanueva
Daniel Villanueva

Reputation: 1

You can use this as example:

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

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

you can just JOIN (or LEFT JOIN) since you're doing TOP 1

SELECT TOP 1
        t1.Col1,
        t1.Col2,
        t3.Col3,
        t3.Col4
FROM    t1
        LEFT OUTER JOIN t2 ON t1.Id = t2.Id
        LEFT OUTER JOIN t3 ON t3.Id = 'z'
WHERE   t1.Id = 'x'
ORDER BY t2.Col2 DESC

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

If the additional query simply returns one row, you can use a CROSS JOIN to append it to your original query:

SELECT TOP 1 t1.Col1, t1.Col2, t3.Col3, t3.Col4
FROM t1 
LEFT OUTER JOIN t2 ON t1.Id = t2.Id 
CROSS JOIN (
   SELECT t3.Col3, t3.Col4 
   FROM t3 
   WHERE t3.Id = 'z') AS t3(Col3, Col4)
WHERE t1.Id = 'x'
ORDER BY t2.Col2 DESC

Upvotes: 1

Related Questions