How can I do a "Join" between two DataTables?
Posted: Sunday, April 18, 2004 11:51 PM
by
datasetfaq
It's tempting to think that with objects like “DataView” and methods like “Select” that DataSets would support this option, but the bottom line is that they don't. A DataView is a sorted and/or filtered list of rows from 1 table only. Also, the Select method is on the DataTable and as such - can't look at other tables.
There are a few options to consider.
- Do the join in SQL. SQL is good at this kind of operation. It would require you to have a new DataTable with the composed list of columns from the two tables - and a DataAdapter with at least a SelectCommand specified to support the fill. You would perform your join in the SelectCommand.CommandText. If you are also retrieving data into 2 separate DataTables for other reasons, then of course you have some redundant data and also some synchronization issues between the 2 individual tables and the joined table. The joined table will not reflect changes in the 2 base tables until you update the database and re-execute the Fill on the joined table.
- Like the above solution, you could create a 3rd table with the composed list of columns in your DataSet but instead of loading from the dataset, you could copy the data from the base tables yourself using the AddRow method. If the two base tables share the same primary key, you could try to “merge” the data from the two DataTable into the 3rd DataTable.
- If the two tables to be joined are in a master detail relationship you can use expression columns to lookup data in a parent DataTable or aggregate records from a Child Table. For example if you have a Customer DataTable with a stateId column, which is a foreign key to a State DataTable with stateId and stateName columns, you can add a computed DataColumn to the Customer DataTable with an expression of Parent.stateName. This new column will be kept in sync if the underlying name changes in the State DataTable or if the stateId is changed on the Customer DataTable to point to a different State. Similarly you can look up values on a child DataTable from a parent DataTable but since there can be 1 or more rows you will typically need aggregates like Sum, Avg, Min, Max, etc. in you expression. The DataColumn.Expression property online help is valuable for the types of expressions you can use.