Creating view using "Union" construct in Select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating view using "Union" construct in Select

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-22 : 08:05:42

Prasanth writes "Hi,
I'm trying to create a view using "UNION" in the select statement in the Query Designer
something like this
SELECT * FROM NOPARTINFO UNION SELECT * FROM NOASSEMBLY UNION SELECT * FROM NOINVENT UNION SELECT * FROM NOMATERIAL;

Error
The Query Designer does not support the UNION SQL construct

How can I solve this problem?
Is there any other way to create view in SQL Server outside the Query Designer?
Or Any other logic which may substitute UNION ?"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-22 : 09:03:30

Easy mate...the view will still be created and will work fine. It just the Graphical component of the Query Designer that will not work. You should be creating views and other queries in Query Analyzer, btw.

Owais

We make a living out of what we get, but we make a life out of what we give.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-22 : 09:10:02

though you must make sure your tables all have the same columns and same info or it will not work or return data that makes much sense ... I highly recommend never using SELECT * with a UNION statement. Always, always explicitly list out the columns you wish to include in the proper order.

Also -- UNION by definition removes duplicates from results .. if that is not desired or not needed, use UNION ALL it will be more efficient.

- Jeff

sqLover
Starting Member

10 Posts

Posted - 2003-12-29 : 16:46:23

Thanks Folks,
Each of the table has same single field calles errors.
These tables are dynamically updated on error
in the aplication.So i want to have the records to be repeated.
I tried in Query analyser and it works
Thanks any way
Prasanth

Its aiways nice to be important
It's more important to be nice always

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-29 : 20:12:05

I have never used the union command for anything, and I write a lot of SQL, what is the advantage or scenario where I would want to us unions rather than inner joins.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-29 : 23:11:06

TSQLMan, here's an example of when you want to use a UNION instead of a JOIN.
Lets say you had three tables, and they all held some sort of people:
Employees
Clients
Star Wars Characters

If your boss asked you "I want a list of all the Employee's Clients and Star Wars folks ordered by the data they entered the system" how would you do that with a join. You can't join Yoda to Bob in accounting. Yoda <> Bob.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

sqLover
Starting Member

10 Posts

Posted - 2003-12-30 : 12:05:54

Hi,
Especially when there is no relationship between
the tables to be "UNIONED" and You want all records
including the ones repeated.

Its aiways nice to be important
It's more important to be nice always

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-12-30 : 12:26:27

Thanks for the explanation.

SELECT tsqlman FROM TableA, TableB

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-12-31 : 06:16:36

Actually, FULL OUTER JOIN can be use used instead of UNION like this:

SELECT DISTINCT COALESCE(A.a, B.a), COALESCE(A.b, B.b)
FROM TableA AS A
FULL OUTER JOIN TableB AS B ON 0 = 1

is equivalent to

SELECT a, b
FROM TableA
UNION
SELECT a, b
FROM TableB

sqLover
Starting Member

10 Posts

Posted - 2004-01-09 : 12:13:54

But Why?

Its aiways nice to be important
It's more important to be nice always

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-11 : 02:28:23

quote:
Originally posted by sqLover

Each of the table has same single field calles errors.

If that statement is true, then I'd suggest you should combine these into one table with perhaps a second field indicating what type of error it was (e.g. No Part Info, No Assembly, etc.) rather than having a bunch of tables all for essentially the same purpose.

Actually, I'd probably also create a Reason Table with the list of reasons to keep track of them separately and to have the flexibility of adding more info about them at a later date.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

sqLover
Starting Member

10 Posts

Posted - 2004-01-12 : 16:26:37

Hi,
Records are inserted / updated into these tables
dynamiclly from a C++ aplication as each error
is occured from different modules.
The application dont have the ability to
recognize what type of an error it is.
Thats why I opted for different tables than
one table. Each table actually has an errorID field too.
Thanks for the suggestion
Regards
Prasanth

Its aiways nice to be important
It's more important to be nice always

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources