[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2451
  • Last Modified:

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.
0
sil-olis
Asked:
sil-olis
1 Solution
 
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
 
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 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now