I have created a dataset with multiple tables including the relationships. I need to query the tables in the dataset to create a table of DISTINCT records that have fields from multiple tables. I would know how to do this with the SQL language, but don't know what functions to use in an existing dataset. A dataview won't work because that only filters a single table. Any iteas?

Hey,

Can you union all of the entire result sets in the SQL query, then use a DataView to filter the result sets? Or, can you do it at the SQL query level? That may be the easiest. To get distinct records from multiple tables, you may have to loop through each table to compare the result sets.

Brian

I think Bmains is right. if you need any reference on union go to
http://www.w3schools.com/sql/sql_union.asp

and reference on select with all kind of options go to.
http://msdn.microsoft.com/library/de...a-ses_9sfo.asp

I'll did all I could, I am doing all I can and I will do all I can. Now, you better do all you can.

bmains,

Thanks for the reply. I can't union them all, because i'd never get the distinct set that I need. I was hoping that I wouldn't have to do the filtering at the SQL query level, because it will mean several specialized stored proceedures, since I am allowing user driven filtering of the main recordset.

I am trying to use the distinct records to populate two different dropdownlists with only the options available in the main recordset.

Hey,

Well then the only other thing I can think of adding is to select the data from one recordset and use a DataView to query the other recordset. Then if there is any rows returned from the DataView delete them from the datatable, or do something with them, whatever your desired action is.

How's that?

Brian

Why you don't use DataRealation object in your dataset..
I think you can declare a DataRealation object then determine your parentcolumn and childcolumn then in your dataset object add your realation object...
here by looping through all the rows of your parent column and retriving them (through GetChildRows("youRealation"))you would have an array that includes your child rows then again you can loop in this array for obtaining your rows corresponding the parent column....
Hope this can help you...

--------------------------------------------
Mehdi
I'm waiting for your better idea.

i was already need to do some thing like that
my problem was to query many tables and i got 2 tables and i cached them and i was need to query them again in .net but failed to easy deal as i write queries so i finaly do that using loops


Ahmed Ali
Software Developer

Look at this link, it got a sample
http://support.microsoft.com/default...b;EN-US;326176