master detail relationship when querying for data

[Abhishek Srivastava]

Hello All,

Suppose if I have a SQL query like

select p.ID, p.NAME, p.UNIT_PRICE, o.QUANTITY from PRODUCT p ORDERS o
where p.ID = X AND P.ID = O.ID

Here one product can have many orders. Essentially Product is the master
table and Orders is the details table.

When I execute this query and populate a DataSet object I get only one
table in the DataSet object.

But this not what is ideal. Ideally I would like to get two tables in
the DataSet one for Products and other for Orders.

I can get two tables is to execute two queries.
select id, name, unit_price from products where id=x
select quantity from orders, products where products.id = x and
orders.id = products.id

But this involves two sql queries.... so not good.

Second solution is to iterate thru the sql data reader and create a
datarows into handbuilt DataSet. if the product id is the same, then
just add another DataRow to the order table, if the product id changes
then, add another DataRow into product table and keep iterating for
orders for this product. But this solution is very error prone and also
it complicates the code.

Another solution is to execute SQL with FOR XML AUTO clause. which
returns XML which contains data in proper relationship. But XML is an
overkill for this kind of solution.

Is there any other solution to this problem none of the above solutions
are very good ones.

regards,
Abhishek.

 

[Nicholas Paldino [.NET/C# MVP]]

Abhishek,

The best solution for this, in fact, is to generate two queries. Your
SQL statement:

select p.ID, p.NAME, p.UNIT_PRICE, o.QUANTITY from PRODUCT p ORDERS o where
p.ID = X AND P.ID = O.ID

Is intended to return just one table. This is one result set, and is
represented by one table.

Why are two queries bad, if they will give you the result that you want?
Depending on how your application is set up, you could do all the processing
yourself to separate out the single table into two tables, but why bother?
SQL server already spent the time putting them together, and it would be a
waste to pull them apart. Basically you are doing twice the work for zero
gain.

I think that you should make two calls, wrapping them in a transaction
if you need to insure the integrity of the data between calls.

Hope this helps.

 

[Girish Chopade]

Hi Abhishek,

I got ur concern.
What should be done in this?
I feel write a Stored Prcedure where in write ur query and get the data in
temporary table.
And then wirite two different select queries and fetch the data from this
temp table.
This way ur procedure will return two different tables.
Using data reletion, u can display this data in repeater or grid or
whatever.

Actually what happened is : though u r writing two different queries it's
all at backend.
This may be little costly but it has to in this way only.

Hope this will be useful...
--
With Regards,
Girish Chopade

Abhishek,

The best solution for this, in fact, is to generate two queries. Your
SQL statement:

select p.ID, p.NAME, p.UNIT_PRICE, o.QUANTITY from PRODUCT p ORDERS o where
p.ID = X AND P.ID = O.ID

Is intended to return just one table. This is one result set, and is
represented by one table.

Why are two queries bad, if they will give you the result that you want?
Depending on how your application is set up, you could do all the processing
yourself to separate out the single table into two tables, but why bother?
SQL server already spent the time putting them together, and it would be a
waste to pull them apart. Basically you are doing twice the work for zero
gain.

I think that you should make two calls, wrapping them in a transaction
if you need to insure the integrity of the data between calls.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello All,

Suppose if I have a SQL query like

select p.ID, p.NAME, p.UNIT_PRICE, o.QUANTITY from PRODUCT p ORDERS o
where p.ID = X AND P.ID = O.ID

Here one product can have many orders. Essentially Product is the master
table and Orders is the details table.

When I execute this query and populate a DataSet object I get only one
table in the DataSet object.

But this not what is ideal. Ideally I would like to get two tables in
the DataSet one for Products and other for Orders.

I can get two tables is to execute two queries.
select id, name, unit_price from products where id=x
select quantity from orders, products where products.id = x and
orders.id = products.id

But this involves two sql queries.... so not good.

Second solution is to iterate thru the sql data reader and create a
datarows into handbuilt DataSet. if the product id is the same, then
just add another DataRow to the order table, if the product id changes
then, add another DataRow into product table and keep iterating for
orders for this product. But this solution is very error prone and also
it complicates the code.

Another solution is to execute SQL with FOR XML AUTO clause. which
returns XML which contains data in proper relationship. But XML is an
overkill for this kind of solution.

Is there any other solution to this problem none of the above solutions
are very good ones.

regards,
Abhishek.