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?
 
knightEknightConnect With a Mentor Commented:
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
 
knightEknightCommented:
Does a union work?

select * from OpenOrders
union
select * from ClosedOrders
0
 
knightEknightCommented:
(this assumes the tables are structurally identical)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Alpesh PatelAssistant ConsultantCommented:
In select Query Make alias for both table (of that field) and create your View.
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.