How to do a Union Query in SQL Server

Dear Experts,

I am using a VB.Net SQLDataSource to populate a combobox on a windows form.  However in the bound combobox, I need to add one additional entry that displays "(none)" and has a value value of 0.  I have tried to follow a couple of posts on the VB side that suggest inserting a new ListItem, but it does not accept ListItem as a valid type.  

So I would like to try doing what I have done in Access previously and use a union query to add a static value row to the query itself.  Something like:  

Select Identified_Language_ID As ID, Print_Name as Name From Identified_Language Order By Print_Name
Union  Select 0 as id, '(none)' as name from identified_Language;

But when I test this in SQL Management Studio, it complains: "Incorrect syntax near the keyword 'UNION'."  Can anyone help me correctly formulate a query that retrieves values from a data table, and adds one additional row with a static value for the two fields?

Thanks for any help.
sil-olisAsked:
Who is Participating?
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.

BrandonGalderisiCommented:
Try
Select Identified_Language_ID As ID, Print_Name as Name From Identified_Language 
Union  
Select 0 as id, '(none)' as name 
Order By Print_Name

Open in new window

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
gigglickCommented:
The problem i believe is the order clause -- also best to enclose key words

Select Identified_Language_ID As ID, Print_Name as [Name] From Identified_Language Union  Select 0 as id, '(none)' as [name] from identified_Language
Order By [Name];
0
gigglickCommented:
Actualyl - you shouldn't need the table name in the union clause either
Select Identified_Language_ID As ID, Print_Name as [Name] From Identified_Language Union  Select 0 as id, '(none)' as [name]
Order By [Name];
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrandonGalderisiCommented:
Yes it is best to enclose keywords in [] but the problem was with the placement of the order by.
0
Göran AnderssonCommented:
The Order By is for the entire query, not for the queries that you unite, so the sort goes after the union.

Use Union All instead of Union, unless you actually want to remove duplicates between the united queries.

Dont select from a table in the second query, just select the data that you want.

Select Identified_Language_ID As ID, Print_Name as Name From Identified_Language
Union All
Select 0, '(none)'
Order By Name
0
BrandonGalderisiCommented:
Depending on the characters that can be in your column, (none) COULD sort later.... to prevent that... you can take this extra step.. but it is probably not necessary
declare @Languages table (Sort int identity(1,1), [ID] int, [name] varchar(32))
insert into @Languages ([id],[name])
values (0,'(none)')
 
insert into @Languages ([id],[name])
Select Identified_Language_ID As ID, Print_Name as Name From Identified_Language 
Order By Print_Name
 
select [id],[name] from @Languages

Open in new window

0
sil-olisAuthor Commented:
Thanks for your speedy replies.  Brandon was the first in and it worked like a charm.
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
Visual Basic.NET

From novice to tech pro — start learning today.