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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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];
Select Identified_Language_ID As ID, Print_Name as [Name] From Identified_Language Union Select 0 as id, '(none)' as [name]
Order By [Name];
Yes it is best to enclose keywords in [] but the problem was with the placement of the order by.
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
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
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 @LanguagesASKER
Thanks for your speedy replies. Brandon was the first in and it worked like a charm.
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];