Derived Tables

OK...I know how to write a query to return for example :

All the people that ordered X and Y

but how do I write one for:

All the people that ordered X but not Y?

Thanks,
TreyO yea...this is how i did the first part

SELECT DISTINCT c.Company
FROM Customers as c
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'X') as temp1
 ON c.CustomerID = temp1.CustomerID
JOIN
 (SELECT CustomerID
 FROM Orders o
 JOIN [Order Details] od
 ON o.OrderID = od.OrderID
 JOIN products p
 ON od.ProductID = p.ProductID
 WHERE p.ProductName = 'Y') as temp2
 ON c.CustomerID = temp2.CustomerID

Derived Columns in one to many relationships

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

MikeOne approach is shown in http://www.mvps.org/access/modules/mdl0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in message
news:cfu2e7$18gm$1@.lettuce.bcit.ca...
> I'm trying to write a query that concatenates multiple records into one
> derived column. Let's say I have an author (Joe Writer) who has written
> three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
> books are in the tblBooks and they are joined by the AuthorID field
> (number). If I use a simple select query to give me the author name and
the
> title, I will get three records, one for each book written.
> What I want is to have all three books combined into one derived column.
So
> if I do the select statement, I will get one column with the author name,
> and the second column will put together all three names of the book
> separated by a column. So it will look like:
> Author Title
> Joe Writer Book 1, Book 2, Book 3,
> Rather than having it appear as 3 records:
> Joe Writer Book 1
> Joe Writer Book 2
> Joe Writer Book 3
> Could someone help me with the SQL involved in this?
> Thanks for the help.
> Cheers,
> Mike|||Mike,

read this article...

http://www.mvps.org/access/modules/mdl0004.htm

It has code that does this.|||Try this out
DECLARE @.BookNames varchar(1000)
SET @.BookNames = ''
SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@.BookNames|||JK (jaikrishnan_nair@.hotmail.com) writes:
> Try this out
> DECLARE @.BookNames varchar(1000)
> SET @.BookNames = ''
> SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
> where author = 'Joe Writer'
> SELECT 'Joe Writer',@.BookNames

This may work. Or not work. The result of this sort of operation is
undefined in SQL Server. This is one of the few situations where
iterating over the data is a better option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Deployment / Version Questions

I'm going to write an application for a friend's business using VS .NET and
SQL Server 2000. I'm confused though as to how I can write everything on my
laptop, then copy to their server. I have an MSDN subscription and did not
realize that I cannot install the Enterprise Edition of SQL Server on XP
Pro. I can install the development edition, but am I then going to run into
problems trying to copy it over to their server?

My experience lies in Informix / DB2 and Java / JDBC, so this is new
territory for me and I appreciate the help.

Derrick LathemHi

On your the destination you will have to install a new copy of SQL that is
correctly licenced. In general I don't think your MSDN licence will not
cover their systems, although you may be able to distribute MSDE. You should
read http://www.microsoft.com/sql/techin...eskChooseEd.asp to
see which edition is most appropriate for your needs. For MSDE check out the
web site http://www.microsoft.com/sql/MSDE/

To get your database onto the destination system there are several ways, the
easiest is probably to detach/attach the database, but you could use one of
several other methods see:
http://support.microsoft.com/defaul...b;en-us;Q314546

You should also make sure that the logins are correctly set up on the
destination system.

John

"lathem" <lathem@.ns.mindspring.com> wrote in message
news:yA3Ob.11830$i4.10805@.newsread1.news.atl.earth link.net...
> I'm going to write an application for a friend's business using VS .NET
and
> SQL Server 2000. I'm confused though as to how I can write everything on
my
> laptop, then copy to their server. I have an MSDN subscription and did
not
> realize that I cannot install the Enterprise Edition of SQL Server on XP
> Pro. I can install the development edition, but am I then going to run
into
> problems trying to copy it over to their server?
> My experience lies in Informix / DB2 and Java / JDBC, so this is new
> territory for me and I appreciate the help.
> Derrick Lathem|||To add to John's response, the SQL Server Developer edition provides the
same features as the Enterprise edition but is licensed only for development
use. This is the appropriate choice for development if you are targeting a
server edition of SQL Server. It is included with MSDN Universal and
Enterprise subscriptions or can be acquired separately for about $50.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"lathem" <lathem@.ns.mindspring.com> wrote in message
news:yA3Ob.11830$i4.10805@.newsread1.news.atl.earth link.net...
> I'm going to write an application for a friend's business using VS .NET
and
> SQL Server 2000. I'm confused though as to how I can write everything on
my
> laptop, then copy to their server. I have an MSDN subscription and did
not
> realize that I cannot install the Enterprise Edition of SQL Server on XP
> Pro. I can install the development edition, but am I then going to run
into
> problems trying to copy it over to their server?
> My experience lies in Informix / DB2 and Java / JDBC, so this is new
> territory for me and I appreciate the help.
> Derrick Lathem

Deploying database with .NET assemblies?

We're going to release a .NET 2.0 application soon (3-4 months) that uses SQL Express 2005 as the backend. We'd like to write some of our SPROC in C# (for obvious reasons!). Our .NET 2.0 app will be deployed using ClickOnce and each customer will have one instance of SQL 2005 Express installed locally.
Is there a way for be to deploy the database with all the C# SPROC "embedded" in it, or I'll have to register assemblies individually? I'm concerned about registering assemblies during installation, especially since we're deploying the application using ClickOnce (but not the database).
Also, from time to time, we need to add stored procedure or modify existing ones. In the "old" .NET 1.1 days, it was easy to do this. We had an assembly which main purpose was to update the database structure and SPROC with simple T-SQL commands (ie: ALTER PROCEDURE). Is there a way for us to achieve something similar with our application if we use C# SPROC?
Another concern I have is that the SQL database will most likely NOT be installed on the same machine as the client application (it will be installed on a server). Sending T-SQL queries worked fine with this scenario before, but now that things have changed, I'd like to know how to achieve such a thing.
Thanks for your help!
Carl
PS: any good book on Yukon out there yet? I can't find any!
Carl,

The best book out there on SQL Server 2005 is "A First Look at SQL Server 2005 for Developers" by Bob Beauchemin, Niels Berglund and Dan Sullivan.

To reload a new version of the assembly, using ALTER ASSEMBLY will work as long as the existing sprocs/functions have the same method signatures.

Deploying the database with the sprocs embedded is no problem. They are just held in varbinary form in a database level system table.

HTH,
Christian|||Christian,
Thanks for your answer! I looked into the Beauchemin's book on Amazon a few days ago and it looks interresting. What is holding me a little is that it's based on a 1 year old beta. Things have probably changed a lot since and might also change again for RTM.
About ALTER ASSEMBLY, do you know if it's possible to ADD managed procedures, functions or modify the signatures in some way? Also, do I need to use ALTER ASSEMBLY from the server machine or it could also work remotely? I assume it would, but I think I'm missing pieces of how this new managed SQL works right now...
Thanks a lot again!
Carl
|||

Carl Mercier wrote:

Christian,
Thanks for your answer! I looked into the Beauchemin's book on Amazon a few days ago and it looks interresting. What is holding me a little is that it's based on a 1 year old beta. Things have probably changed a lot since and might also change again for RTM.

As a co-author of the book, I can definitely say it is interesting :-). Yes, it is based on a one year old beta, and much have changed. However, we do have an errata page as well as a page for changes, so you'll always be able to see the latest info.

Carl Mercier wrote:

About ALTER ASSEMBLY, do you know if it's possible to ADD managed procedures, functions or modify the signatures in some way? Also, do I need to use ALTER ASSEMBLY from the server machine or it could also work remotely? I assume it would, but I think I'm missing pieces of how this new managed SQL works right now...

Sure you can add managed procedures, after running ALTER ASSEMBLY you just run CREATE FUNCTION/PROCEDURE/TRIGGER against your new methods. You are NOT allowed to modify signatures.
ALTER will work remotely, BUT; notice that the SqlServer project (which are used to create assemblies for SQL Server) in Visual Studio does not have functionality for ALTER assembly. That's one of the reasons I created my "yukondeployment" MSBUILD tasks and templates for Visual Studio. You can download it and read more about it here.
Niels|||Thanks for your help! I will definately check out Yukon Deployment!
I didn't know you were co-author of the book. I thought you deserved one more sale so I went ahead and ordered the book. Hope it arrives in time for my vacation! I'll read it on the beach in a few days! I hope this sale will at least buy you a cold one.
Your help is appreciated!
Carl