Combining output of two or more select statement

Question

How to combine output of two or more SELECT statements, I have multiple tables which are having some data that I need to fetch them so I write multiple SELECT query. Now I want to combine result of the queries so what do I need to do ? I want the output to be:

t1.qty,t2.qty,t3.qty 

Trossachs

Can you show us your queries? And what is your expected result? – Siclari

Did you look at UNION – Physician

Are you looking at joining 3 tables together, or merging 3 results set with the same columns? – Irons

@Ø Hanky Panky Ø .qty actually looks like it's a JOIN.. – Siclari

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 I am using this kind of query it work for on two level of join but I need to use more than two level . please suggest something like this so I can use – Trossachs

Answer 1

One option would be:

SELECT (SELECT SUM(qty) FROM Table1 WHERE ...),
       (SELECT SUM(qty) FROM Table2 WHERE ...),
       (SELECT SUM(qty) FROM Table3 WHERE ...)

Another would be joining, provided that there is a link:

SELECT * 
FROM   (SELECT ID,SUM(qty) FROM Table1 GROUP BY ID) T1
       JOIN (SELECT ID,SUM(qty) FROM Table2 GROUP BY ID) T2
           ON T1.ID = T2.ID
       JOIN (SELECT ID,SUM(qty) FROM Table3 GROUP BY ID) T3
           ON T1.ID = T3.ID

The above options would be to display results in one row.

You may need union to combine rows:

SELECT qty FROM Table1
UNION
SELECT qty FROM Table2
UNION
SELECT qty FROM Table3

Much more options if you define more specific needs

Hyperkeratosis

col t1.qty,t2.qty,t3.qty and row as PUN ,KOL,SOL – Trossachs

What is PUN, KOL, SOL ? – Hyperkeratosis

I think those are the aliases he wants for the columns – Siclari

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 I am using this kind of query it work for on two level of join but I need to use more than two level . please suggest something like this so I can use. – Trossachs

You should add that on your original post for everyone to see. – Hyperkeratosis

@Giannis Paraskevopoulos: which of these solutions do you consider, in general, best practice? – Illboding

@matt It depends on your case i believe. I guess i would go for option 1 if i wanted the results in one row and option 3 if i wanted the results in separate ones, but it really depends on the specifics of the case. The question here was quite too broad. – Hyperkeratosis

In place of ID I need date is it possible. if yes can u plz help – Oily

@Oily i think you should explain a bit your case. – Hyperkeratosis

I am having 4 tables(A,B,C,D) with columns are date and amount. My requirement is i need sum of amount from each table group by date. Means resultant table columns should be date, sum(A),sum(B),sum(C),sum(D) – Oily

Then i believe my second sample (joining of the grouped results on date) would be what you want. Just replace id with the date column and qty with the amount column. You would join on the date column. – Hyperkeratosis

Join with ID is OK.. but with date is it accepts or not is my doubt – Oily

Well, I wouldn't like it either, but you need to see what is in common. Since you do not have something else.... Then date it is. Make sure it does not have time portions. – Hyperkeratosis

Only date is the common field. and i will try with that removing time portion. almost 6 hrs this making me to think new each time. Thank you lot sir for your wonderful support – Oily

If you are having trouble removing the time check this answer here #1177949 – Hyperkeratosis

I am using Convert(nvarchar(15),date,103)date is it OK – Oily

I usually go for convert(varchar(8),datefield,112) it gets yyyymmdd format. Then you may cast it to int. – Hyperkeratosis

Answer 2

Why not create a statement that will fetch them all at once?

SELECT tableA.data1, tableB.data2 FROM tableA, tableB WHERE <condition here>

Clardy

Answer 3

task1 = "select top " & strmcount & " * from nregistration where " & strtotal1
task2 = "select top " & strfcount & " * from nregistration where " & strtotal2

to make execute on parallel

Watteau