CODEWITHSUNDEEP
sql-server-2008
Lobuno
Lobuno

Reputation: 1405

"Merging" columns from several tables

Using SQL Server 2008, suppose I have several tables with 3 common columns (not related):

TABLE1

col1 colSomeOther col2 colAnotherOne

TABLE2

col1 colSomeOther col2 colAnotherOne

TABLE3

col1 colSomeOther col2 colAnotherOne

I would like to create a view which merges col1 and 2 for the 3 tables above. Something like:

VIEW

col1   col2

where col1 contains ALL elements from table 1, 2 and 3, and col2 contains ALL elements from col2 in table 1, 2, 3.

Is this possible?

Upvotes: 0

Views: 111

Answers (2)

KeithS
KeithS

Reputation: 71573

Yep. This is a "union"; multiple result sets of the same "signature" (number and type of data columns), concatenated one after the other. The query to do this is as simple as:

SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col1, col2 FROM TABLE2
UNION ALL
SELECT col1, col2 FROM TABLE3

If you want the query to "de-duplicate" the results, returning only unique rows, omit the "ALL" keywords from the unions. With the ALL keywords, it simply tacks on the results of each SELECT to the combined result set, including rows from Table2 that may have exactly the same data as Table1.

Upvotes: 1

Xint0
Xint0

Reputation: 5399

I think you are asking for an UNION:

select col1, col2 from table1
UNION ALL
select col1, col2 from table2
UNION ALL
select col1, col2 from table3

Should work as long as col1 and col2 have compatible data types across all three tables.

If you want to eliminate duplicate rows then use UNION instead of UNION ALL.

Upvotes: 0

Related Questions