ASP.NET, SQL Server, WPF, C#, VB.NET

Hi,  
  
I have the following query:  
  
SELECT  
  
C.CompanyName,  
  
O.OrderDate,  
  
E.LastName AS 'Registered By'  
  
FROM Customers C  
  
JOIN Orders O  
  
ON C.CustomerID=O.CustomerID  
  
JOIN Employees E  
  
ON E.EmployeeID=O.EmployeeID  
  
ORDER BY C.CompanyName  
  
I need all information of orders for particular company, appear under the  
  
element of that company but the query that I have written returns the name  
  
of company again and again for each order:  
  
SELECT  
  
C.CompanyName AS "@Company",  
  
O.OrderDate AS "Order/OrderDate",  
  
E.LastName AS "Order/RegisteredBy"  
  
FROM Customers C  
  
JOIN Orders O  
  
ON C.CustomerID=O.CustomerID  
  
JOIN Employees E  
  
ON E.EmployeeID=O.EmployeeID  
  
ORDER BY C.CompanyName  
  
FOR XML PATH  
  
Any help would be greatly appreciated.  
  
Leila

oops! seems that the problem solved:  
  
SELECT  
  
C.CompanyName AS "@Company",  
  
(SELECT  
  
O.OrderDate AS 'Order/OrderDate',  
  
E.LastName AS 'Order/RegisteredBy'  
  
FROM Orders O  
  
JOIN Employees E  
  
ON E.EmployeeID=O.EmployeeID  
  
WHERE O.CustomerID=C.CUstomerID  
  
FOR XML PATH(''),type)  
  
FROM Customers C  
  
ORDER BY C.CompanyName  
  
FOR XML PATH('Customers'),ROOT('ROOT')  
  
Thanks to BOL documentation team :-)  
  
"Leila" <Leilas@hotpop.com> wrote in message  
  
news:%23AjU8w2IGHA.1676@TK2MSFTNGP09.phx.gbl...

Good documentation can help ;)  
  
You can make your query slightly easier to maintain by writing the subquery  
  
as  
  
(SELECT  
  
O.OrderDate AS 'OrderDate',  
  
E.LastName AS 'RegisteredBy'  
  
FROM Orders O  
  
JOIN Employees E  
  
ON E.EmployeeID=O.EmployeeID  
  
WHERE O.CustomerID=C.CUstomerID  
  
FOR XML PATH('Order'),type)  
  
Best regards  
  
Michael  
  
"Leila" <Leilas@hotpop.com> wrote in message  
  
news:%23LnD5B3IGHA.3696@TK2MSFTNGP15.phx.gbl...