SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Joining Data from a table to a list of other Items
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sirmilt
Starting Member

USA
49 Posts

Posted - 09/06/2013 :  19:54:57  Show Profile  Reply with Quote
I am Using an SQL Server 2008 R2 database with Visual Basic 2010. I use the following stored procedure;
SELECT DISTINCT Category FROM tblBooks ORDER BY Category;This works fine except if its a new User with an empty or near empty table there will be nothing or very little for the User to select.

The following was recommended:
SELECT DISTINCT Category FROM tblBooks UNION SELECT 'Add In 1' AS Category, 'Add In 2' As Category ORDER BY Category ORDER BY Category;
There will be about 5 or 6 items to be added this way.

When testing in SSMS the code throws an error as shown below:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I'm relly not sure what they are telling me or if the sql I am trying to use is the right method in doing it.

Any help would be really appreciated



Milt

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/07/2013 :  10:37:46  Show Profile  Reply with Quote
Not entirely sure because it looks ok except that you do have too many ORDER BY clauses.
Go to Top of Page

sirmilt
Starting Member

USA
49 Posts

Posted - 09/07/2013 :  10:58:50  Show Profile  Reply with Quote
Thanks for the response,

The extra "Order By" was m error in writing the original post. only one is in the actual statement.

Milt
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 09/09/2013 :  15:31:17  Show Profile  Reply with Quote
SELECT DISTINCT Category FROM tblBooks 
UNION SELECT Category FROM (VALUES ('Add In 1'),('Add In 2')) As A(Category) 
ORDER BY Category ORDER BY Category;

You need to split the select up to be several

djj

Edited by - djj55 on 09/09/2013 15:46:55
Go to Top of Page

sirmilt
Starting Member

USA
49 Posts

Posted - 09/10/2013 :  09:22:35  Show Profile  Reply with Quote
djj

Thanks for the tip, it worked perfectly.

Milt

Milt
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 09/10/2013 :  10:48:57  Show Profile  Reply with Quote
Glad I could help.

Had to jump through a couple of hoops to find how to due this as I knew I had seen it but when I first tried got the wrong syntax. :-)

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000