Ad:

MS SQL Discussion Thread
Unsolved
Views: 23695

Dec 15th, 2008

How To Combine Multiple Query?

Expand Post »

hello Guys,

I facing a sql query problem which is i need to join many query into one query. That is :

a)

sql Syntax (Toggle Plain Text)
SELECT sum(Total) As [MONEY1]
FROM
((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL
SELECT sum(Total) As [MONEY1]
FROM
((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL

sql Syntax (Toggle Plain Text)
SELECT sum(AllTotal) as [MONEY2]
(SELECT SUM(T5.Total+T6.Total) AS ALLTotal FROM TABLE1 T5 INNER TABLE6 T6 ON T5.ID = T6.ID) UNION ALL 
(SELECT SUM(T7.Total+T8.Total) AS ALLTotal FROM TABLE7 T7 INNER TABLE8 T8 ON T7.ID = T8.ID ) DERIVETBL
Select sum(AllTotal) as [MONEY2]
(SELECT SUM(T5.Total+T6.Total) AS ALLTotal FROM TABLE1 T5 INNER TABLE6 T6 ON T5.ID = T6.ID) UNION ALL 
(SELECT SUM(T7.Total+T8.Total) AS ALLTotal FROM TABLE7 T7 INNER TABLE8 T8 ON T7.ID = T8.ID ) DERIVETBL

My Question is how i can combine (a) query and (b) query into into query, something like :
SELECT 'Testing' as [Title] , [MONEY1] , [MONEY2]

Last edited by peter_budo; Dec 15th, 2008 at 6:38 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Similar Threads

Multiple object records forming one query in MS SQL
Xpath in XML, XSLT and XPATH
Read multiple column data from SQL query ( 1 row ) in ASP.NET
Combining a SELECT query with a SELECT SUBSTRING query in MySQL

nokomoli

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Nov 2007

Permalink

Dec 22nd, 2008

Re: How To Combine Multiple Query?

First SQL Query
UNION
Second SQL Query

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Dec 26th, 2008

Re: How To Combine Multiple Query?

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
SQL UNION Example

Look at the following tables:

"Employees_Norway":
E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

"Employees_USA":
E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).

The syntax is as follows:

[SQL Statement 1]
UNION
[SQL Statement 2]

Say we have the following two tables,

Table Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

Table Internet_Sales
Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

Result:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

Jenniferlinn

Reputation Points: 7

Solved Threads: 1

Light Poster

Offline

25 posts
since Dec 2008

Permalink

Dec 27th, 2008

Re: How To Combine Multiple Query?

Try this query below:

MS SQL Syntax (Toggle Plain Text)
SELECT 'Testing' AS [Title] , [MONEY1] , [MONEY2] 
  FROM (SELECT sum(Total) AS [MONEY1] FROM((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL) X1, (SELECT sum(AllTotal) AS [MONEY2](SELECT SUM(T5.Total+T6.Total) AS ALLTotal FROM TABLE1 T5 INNER TABLE6 T6 ON T5.ID = T6.ID) UNION ALL (SELECT SUM(T7.Total+T8.Total) AS ALLTotal FROM TABLE7 T7 INNER TABLE8 T8 ON T7.ID = T8.ID ) DERIVETBL) X2
SELECT 'Testing' as [Title] , [MONEY1] , [MONEY2] 
  from (SELECT sum(Total) AS [MONEY1] FROM((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL) X1, (SELECT sum(AllTotal) AS [MONEY2](SELECT SUM(T5.Total+T6.Total) AS ALLTotal FROM TABLE1 T5 INNER TABLE6 T6 ON T5.ID = T6.ID) UNION ALL (SELECT SUM(T7.Total+T8.Total) AS ALLTotal FROM TABLE7 T7 INNER TABLE8 T8 ON T7.ID = T8.ID ) DERIVETBL) X2

Last edited by huangzhi; Dec 27th, 2008 at 12:49 am.

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: mySQL UPDATE is adding erroneous spaces, special characters

Next Thread in MS SQL Forum Timeline: Return all Master and any child records

DaniWeb Message

Cancel Changes

User Name
Password