SQL Query Combine Tables

I am writing a SQL 2005 Query and am having difficulty seeing contents of two tables with similar information. The two tables I am working with have the same columns (labeled the same). One table is for open orders and the other is for closed orders. I am trying to write a query that will show all of the orders at the same time.

I know I can have a column with alias 'Order Number Open' and a column called 'Order Number Closed' but I want to see all of the order numbers in the same 'Order Number' Column.

Is this a function of JOIN? Or maybe I need to make a view? (I don't know how to make views with Management Studio 2005)

Thanks for all your help experts!
LVL 5
Matt CoughlinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

knightEknightCommented:
Does a union work?

select * from OpenOrders
union
select * from ClosedOrders
0
knightEknightCommented:
(this assumes the tables are structurally identical)
0
Alpesh PatelAssistant ConsultantCommented:
In select Query Make alias for both table (of that field) and create your View.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Matt CoughlinAuthor Commented:
Here is the select statement that I am using. If I show ORDNUMBER from one table it says NULL wherever the other tables numbers should be.

I will try the union and see what happens.
SELECT     ORDOPEN.ORDNUMBER
FROM         ORDOPEN FULL OUTER JOIN
                      ORDCLOSED ON ORDOPEN.ORDNUMBER = ORDCLOSED.ORDNUMBER

Open in new window

0
knightEknightCommented:
select ORDNUMBER
from (
   select ORDNUMBER from ORDOPEN
   union
   select ORDNUMBER from ORDCLOSED
) tbl
order by 1
0
Matt CoughlinAuthor Commented:
The union statement gave me this error.

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
0
knightEknightCommented:
also fyi, in general when using unions, it is best to specify all the columns explicitly, instead of doing  "select *"  as I did above.
0
Matt CoughlinAuthor Commented:
Ok, I will try that statement.
0
knightEknightCommented:
That error means the number of columns in each table is different, so don't use SELECT *
instead, specify the columns, as in the query above.
0
Matt CoughlinAuthor Commented:
Alright! That worked, I now have a new table with the combined results.

So... if I wanted all of the shared fields I would add them into the union statement, correct?

select ORDNUMBER, ORDDATE, ORDAMOUNT
from (
   select ORDNUMBER, ORDDATE, ORDAMOUNT from ORDOPEN
   union
   select ORDNUMBER, ORDDATE, ORDAMOUNT from ORDCLOSED
) ORDERS
order by 1
0
knightEknightCommented:
exactly!  :)

... and it may be as simple as this:

   select ORDNUMBER, ORDDATE, ORDAMOUNT from ORDOPEN
   union
   select ORDNUMBER, ORDDATE, ORDAMOUNT from ORDCLOSED
   order by 1
0
Matt CoughlinAuthor Commented:
Ok, now the tricky part... is it possible to make a view with this so that I only have to call the unioned table any time I need it rather than build it again and again?
0
knightEknightCommented:
create view OrdersView as
   select ORDNUMBER, ORDDATE, ORDAMOUNT from ORDOPEN
   union
   select ORDNUMBER, ORDDATE, ORDAMOUNT from ORDCLOSED
0
knightEknightCommented:
and if you want to know if they are opened or closed:

 create view OrdersView as
   select ORDNUMBER, ORDDATE, ORDAMOUNT, 'Open' as OrderStatus from ORDOPEN
   union
   select ORDNUMBER, ORDDATE, ORDAMOUNT, 'Closed' as OrderStatus from ORDCLOSED
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt CoughlinAuthor Commented:
sweeeet!

Thanks so much!
0
knightEknightCommented:
... in which case, these two queries would produce identical results:

select ORDNUMBER, ORDDATE, ORDAMOUNT
from OrdersView
where OrderStatus = 'Open'

select ORDNUMBER, ORDDATE, ORDAMOUNT
from ORDOPEN
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.