Dataview with Datas from several Tables

by KaiY at 2013-01-22 07:51:04

Hi Guys,

I need some help scripting a query from two or more datatables.

I’ve tried to create an example to show you my problem.

I create a dataset ($DataSet) containing 2 Datatables ($Table_1 and $Table_2) connected by the primarykey "name".
Could you help me to get a query datas from both tables.

Thank you very much and best greetings from Germany

$DataSet = New-Object System.Data.DataSet
$Table_1 = $DataSet.Tables.Add("Table_1")
$Table_2 = $DataSet.Tables.Add("Table_2")

$Properties_1 = @("Name","Salary","Country")
$Properties_1 | foreach {
$Column = New-Object System.Data.DataColumn($)

$Properties_2 = @("Name","Car","Fuel")
$Properties_2 | foreach {
$Column = New-Object System.Data.DataColumn($
$Table_1.PrimaryKey = $Table_1.Columns["Name"]
$Table_2.PrimaryKey = $Table_2.Columns["Name"]

" "
$Null=$Table_2.Rows.Add("Napf","BMW","Super Petrol")

$DataView = New-Object System.Data.DataView($Table_1)
$DataView | ft Name, Country -Auto [color=#BF0040] #e.g. column "car" from table_2 should appear here, too[/color]
by DonJ at 2013-01-22 13:42:02
This is probably something better answered by the folks on; it’s really a .NET Framework question unrelated to PowerShell. Sorry I couldn’t be of more help.
by nohandle at 2013-01-23 10:43:11
first of all I have to say I like your clean writing style, except personally I’d rather use ‘> $null’ than ‘$null=’. For me it just seems to better describe what is happening.
[quote="KaiY"]#e.g. column "car" from table_2 should appear here, too[/quote] As I see it you need to do a simple join of the tables (I am no db whiz :slight_smile: ) and for simple join you can use Merge method.
$baseTable = $Table_1.Copy()
$baseTable | ft -AutoSize

Name Salary Country Car Fuel
---- ------ ------- — ----
Huber 45000 USA Mercedes Diesel
Mayer 66000 Germany
Napf 53000 France BMW Super Petrol
Bauer Honda Petrol
by KaiY at 2013-01-23 14:42:15
thank you for this smart solution. It works exactly as I need it.

I got another solution, which also works but doesn’t look so smart as yours

$Relation1 = New-Object System.Data.DataRelation( "bothTables", $Column1, $Column2 )

ForEach($ParentRow in $Relation1.ParentTable.Rows) {
ForEach($ChildRow in $ParentRow.GetChildRows($Relation1)) {
$a += new-Object -TypeName PSObject -Property @{Name=$ParentRow["Name"];Salary=$ParentRow["Salary"];Country=$ParentRow["Country"];car=$ChildRow["car"];fuel=$ChildRow["fuel"]}
$a | ft * -auto
by nohandle at 2013-01-24 08:32:00
Great that worked out for you. Just don’t forget my solution is probably pretty limited. As Don suggested, asking the experienced devs on other forum is really a good idea.