I have 2 tables in my database (orders, customers). Not all customers have
orders, but all orders have customers.

When I run the following code, the XML that is returned shows a list of all
orders, and the corresponding customers nested within each order (which is
kind of what i want). BUT, it is also returning customers that don't have
an order.

how can i structure my code so only all orders are returned with
corresponding customers, rather than all orders and all customers.

thanks.

DataSet oDataSet = new DataSet("CustomerOrder");
SqlCommand oCmd = new SqlCommand("select * from orders");
oCmd.Connection = oConn;

SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
oAdapter.Fill (oDataSet, "Order");

oCmd.CommandText = "select * from customers";
oAdapter.SelectCommand = oCmd;
oAdapter.Fill (oDataSet, "Customer");

DataRelation oRelation = oDataSet.Relations.Add ("OrderCustomer",
oDataSet.Tables["Order"].Columns["CustomerId"],
oDataSet.Tables["Customer"].Columns["CustomerId"],
false);

oRelation.Nested = true;

return oDataSet.GetXml();

A simple way would be to restructure the SQL queries.
Assuming you want two seperate tables change the first
query to return values based on a join of the customers
and orders. The join will eliminate any customers that do
not have orders leaving you with a table of customers with
orders and a table of all the orders.

Likewise you could use the IN with in the first SQL select

SELECT * FROM customers WHERE CustomerID IN (SELECT
CustomerID FROM orders)

Again the results should be the same without the
generation of a join directly in the SQL


>-----Original Message-----
>I have 2 tables in my database (orders, customers). Not
all customers have
>orders, but all orders have customers.
>
>When I run the following code, the XML that is returned
shows a list of all
>orders, and the corresponding customers nested within
each order (which is
>kind of what i want). BUT, it is also returning
customers that don't have
>an order.
>
>how can i structure my code so only all orders are
returned with
>corresponding customers, rather than all orders and all
customers.
>
>thanks.
>
>DataSet oDataSet = new DataSet("CustomerOrder");
>SqlCommand oCmd = new SqlCommand("select * from orders");
>oCmd.Connection = oConn;
>
>SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
>oAdapter.Fill (oDataSet, "Order");
>
>oCmd.CommandText = "select * from customers";
>oAdapter.SelectCommand = oCmd;
>oAdapter.Fill (oDataSet, "Customer");
>
>DataRelation oRelation = oDataSet.Relations.Add
("OrderCustomer",
>oDataSet.Tables["Order"].Columns["CustomerId"],
>oDataSet.Tables["Customer"].Columns["CustomerId"],
>false);
>
>oRelation.Nested = true;
>
>return oDataSet.GetXml();
>
>
>
>.
>

If I perform an inner join in my 1st query and get rid of the 2nd query
completely, then it works but the XML is not nested.

Both orders and customers are on the same hierachical level in the XML. Any
ideas how I can get the Customer nested within the order?


"Perley" <(E-Mail Removed)> wrote in message
news:0a7a01c34c57$ea900a80$(E-Mail Removed)...
> A simple way would be to restructure the SQL queries.
> Assuming you want two seperate tables change the first
> query to return values based on a join of the customers
> and orders. The join will eliminate any customers that do
> not have orders leaving you with a table of customers with
> orders and a table of all the orders.
>
> Likewise you could use the IN with in the first SQL select
>
> SELECT * FROM customers WHERE CustomerID IN (SELECT
> CustomerID FROM orders)
>
> Again the results should be the same without the
> generation of a join directly in the SQL
>
>
> >-----Original Message-----
> >I have 2 tables in my database (orders, customers). Not
> all customers have
> >orders, but all orders have customers.
> >
> >When I run the following code, the XML that is returned
> shows a list of all
> >orders, and the corresponding customers nested within
> each order (which is
> >kind of what i want). BUT, it is also returning
> customers that don't have
> >an order.
> >
> >how can i structure my code so only all orders are
> returned with
> >corresponding customers, rather than all orders and all
> customers.
> >
> >thanks.
> >
> >DataSet oDataSet = new DataSet("CustomerOrder");
> >SqlCommand oCmd = new SqlCommand("select * from orders");
> >oCmd.Connection = oConn;
> >
> >SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
> >oAdapter.Fill (oDataSet, "Order");
> >
> >oCmd.CommandText = "select * from customers";
> >oAdapter.SelectCommand = oCmd;
> >oAdapter.Fill (oDataSet, "Customer");
> >
> >DataRelation oRelation = oDataSet.Relations.Add
> ("OrderCustomer",
> >oDataSet.Tables["Order"].Columns["CustomerId"],
> >oDataSet.Tables["Customer"].Columns["CustomerId"],
> >false);
> >
> >oRelation.Nested = true;
> >
> >return oDataSet.GetXml();
> >
> >
> >
> >.
> >

Suzy,

oCmd.CommandText = "select * from customers";

Air code

oCmd.CommandText = "select * from customers where CustomerID in (Select
CustomerID From Orders)";


--
Kathleen (MVP-VB)



"suzy" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> I have 2 tables in my database (orders, customers). Not all customers
have
> orders, but all orders have customers.
>
> When I run the following code, the XML that is returned shows a list of
all
> orders, and the corresponding customers nested within each order (which is
> kind of what i want). BUT, it is also returning customers that don't have
> an order.
>
> how can i structure my code so only all orders are returned with
> corresponding customers, rather than all orders and all customers.
>
> thanks.
>
> DataSet oDataSet = new DataSet("CustomerOrder");
> SqlCommand oCmd = new SqlCommand("select * from orders");
> oCmd.Connection = oConn;
>
> SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
> oAdapter.Fill (oDataSet, "Order");
>
> oCmd.CommandText = "select * from customers";
> oAdapter.SelectCommand = oCmd;
> oAdapter.Fill (oDataSet, "Customer");
>
> DataRelation oRelation = oDataSet.Relations.Add ("OrderCustomer",
> oDataSet.Tables["Order"].Columns["CustomerId"],
> oDataSet.Tables["Customer"].Columns["CustomerId"],
> false);
>
> oRelation.Nested = true;
>
> return oDataSet.GetXml();
>
>
>

Without using a query to make sure that the dataset only contains customers
with orders (which would presumably be contrary to why you have all the
customers loaded in the first place) the only way you are going to be able
to do this is walk through each row in the parent table (customer) and write
out it's XML only if the parent row's GetChildren() method returns you
something useful.

Not particularly elegant, but not too tricky to achieve either. Good luck

--
Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.


_____________________________
"suzy" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I have 2 tables in my database (orders, customers). Not all customers
have
> orders, but all orders have customers.
>
> When I run the following code, the XML that is returned shows a list of
all
> orders, and the corresponding customers nested within each order (which is
> kind of what i want). BUT, it is also returning customers that don't have
> an order.
>
> how can i structure my code so only all orders are returned with
> corresponding customers, rather than all orders and all customers.
>
> thanks.
>
> DataSet oDataSet = new DataSet("CustomerOrder");
> SqlCommand oCmd = new SqlCommand("select * from orders");
> oCmd.Connection = oConn;
>
> SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
> oAdapter.Fill (oDataSet, "Order");
>
> oCmd.CommandText = "select * from customers";
> oAdapter.SelectCommand = oCmd;
> oAdapter.Fill (oDataSet, "Customer");
>
> DataRelation oRelation = oDataSet.Relations.Add ("OrderCustomer",
> oDataSet.Tables["Order"].Columns["CustomerId"],
> oDataSet.Tables["Customer"].Columns["CustomerId"],
> false);
>
> oRelation.Nested = true;
>
> return oDataSet.GetXml();
>
>
>