SQL question

How do you combine two SELECT statements from two different tables
into one result (assuming bioth tables have the exact same fields)?

Like, assuming that the query SELECT * FROM Table1 gives the result

ID Name
001 Smith
002 Jones

and the query SELECT * FROM Table2 gives the result

ID Name
247 Simpson
856 Beckley

I want a SELECT ... ORDER BY ID statement that will output the
following data:

ID Name
001 Smith
002 Jones
247 Simpson
856 Beckley

How do I do this?

Re: SQL question

On Jul 7, 2:59 pm, dwightarmyofchampi...@hotmail.com wrote:
> How do you combine two SELECT statements from two different tables
> into one result (assuming bioth tables have the exact same fields)?
>
> Like, assuming that the query SELECT * FROM Table1 gives the result
>
> ID Name
> 001 Smith
> 002 Jones
>
> and the query SELECT * FROM Table2 gives the result
>
> ID Name
> 247 Simpson
> 856 Beckley
>
> I want a SELECT ... ORDER BY ID statement that will output the
> following data:
>
> ID Name
> 001 Smith
> 002 Jones
> 247 Simpson
> 856 Beckley
>
> How do I do this?

select id, name
from table1
union
select id name
from table2
order by id

Re: SQL question

ZeldorBlat (zeldorblat@gmail.com) writes:
> On Jul 7, 2:59 pm, dwightarmyofchampi...@hotmail.com wrote:
>> I want a SELECT ... ORDER BY ID statement that will output the
>> following data:
>>
>> ID Name
>> 001 Smith
>> 002 Jones
>> 247 Simpson
>> 856 Beckley
>>
>> How do I do this?
>
> select id, name
> from table1
> union
> select id name
> from table2
> order by id

Or:

select id, name
from table1
union ALL
select id name
from table2
order by id

By default UNION sorts out duplicates. With UNION ALL they are retained.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL question

>> How do you combine two SELECT statements from two different tables into one result (assuming both tables have the exact same fields [sic])? <<

The short answer is to use a UNION or UNION ALL.

The right answer is that tables are not files, just as columns are not
fields. If two tables have the exact same structure, then they model
the exact same entity and should be in one table. Files do not behave
that way.

This is usually the result of attribute splitting -- taking the values
of an attribute and making a table for each value.