Inner Join in PowerShell (without SQL)

Paweł Dyl provided you a solution based on your two tables. However you probably need a generic solution where you don't have to specify each property by name yourself.

I would combine each table to a an array. Group the tables on the Name property using the Group-Object cmdlet. Iterate over each group and create a PsObject using the properties:

$table1 = [PSCustomObject]@{ Name = 'Abc'; Group = 'Bad'; Policy = 'Great'}, [PSCustomObject]@{ Name = 'redi'; Group = 'Good'; Policy = 'MAD'}$table2 = [PSCustomObject]@{ Name = 'Abc'; Limit = '10'; used = '5'}, [PSCustomObject]@{ Name = 'redi'; Limit = '20'; used = '1'}$allTables = $table1 + $table2$allTables | group Name | Foreach {    $properties = @{}    $_.Group | Foreach {        $_.PsObject.Properties | Where Name -ne 'Name' | Foreach {            $properties +=  @{                "$($_.Name)" = "$($_.Value)"            }        }    }    $properties += @{Name = $_.Name}    New-Object PSObject –Property $properties}

Output:

Group  : BadPolicy : GreatName   : AbcLimit  : 10used   : 5Group  : GoodPolicy : MADName   : rediLimit  : 20used   : 1

You can use simple loop join as follows:

$table1 = [pscustomobject]@{Name='Abc';Group='Bad';Policy='Great'},[pscustomobject]@{Name='redi';Group='Good ';Policy='MAD'}$table2 = [pscustomobject]@{Name='Abc';Limit=10;used=5},[pscustomobject]@{Name='redi';Limit=20;used=1}$table1 | % {                 foreach ($t2 in $table2) {                    if ($_.Name -eq $t2.Name) {                        [pscustomobject]@{Name=$_.Name;Group=$_.Group;Policy=$_.Policy;Limit=$t2.Limit;Used=$t2.Used}                    }                }            }

Assuming uniqueness of keys you can also use faster, hashtable approach:

$hashed = $table1 | group Name -AsHashTable$table2 | % {    $matched = $hashed[$_.Name]    if ($matched) {        [pscustomobject]@{Name=$matched.Name;Group=$matched.Group;Policy=$matched.Policy;Limit=$_.Limit;Used=$_.Used}    }}

You can also use generic solution and wrap it in function. It matches records by their property names:

function Join-Records($tab1, $tab2){    $prop1 = $tab1 | select -First 1 | % {$_.PSObject.Properties.Name} #properties from t1    $prop2 = $tab2 | select -First 1 | % {$_.PSObject.Properties.Name} #properties from t2    $join = $prop1 | ? {$prop2 -Contains $_}    $unique1 = $prop1 | ?{ $join -notcontains $_}    $unique2 = $prop2 | ?{ $join -notcontains $_}    if ($join) {        $tab1 | % {            $t1 = $_            $tab2 | % {                $t2 = $_                foreach ($prop in $join) {                    if (!$t1.$prop.Equals($t2.$prop)) { return; }                }                $result = @{}                                $join | % { $result.Add($_,$t1.$_) }                $unique1 | % { $result.Add($_,$t1.$_) }                $unique2 | % { $result.Add($_,$t2.$_) }                [PSCustomObject]$result            }        }    }}$table1 = [pscustomobject]@{Name='Abc';Group='Bad';Policy='Great'},    [pscustomobject]@{Name='redi';Group='Good ';Policy='MAD'},    [pscustomobject]@{Name='Not joined';Group='Very bad';Policy='Great'}$table2 = [pscustomobject]@{Name='Abc';Limit=10;used=5},    [pscustomobject]@{Name='redi';Limit=20;used=1},    [pscustomobject]@{Name='redi';Limit=20;used=2}#name is only common property, records joined by nameJoin-Records $table1 $table2#example2$test1 = [pscustomobject]@{A=1;B=1;C='R1'},    [pscustomobject]@{A=1;B=2;C='R2'},    [pscustomobject]@{A=2;B=2;C='R3'}$test2 = [pscustomobject]@{A=1;B=1;D='R4'},    [pscustomobject]@{A=3;B=2;D='R5'},    [pscustomobject]@{A=4;B=2;D='R6'}Join-Records $test1 $test2 #joined by two common columns - A and B

You can also cascade calls:

$test1 = [pscustomobject]@{A=1;B=1;C='R1'},    [pscustomobject]@{A=1;B=2;C='R2'},    [pscustomobject]@{A=2;B=2;C='R3'}$test2 = [pscustomobject]@{A=1;B=1;D='R4'},    [pscustomobject]@{A=3;B=2;D='R5'},    [pscustomobject]@{A=4;B=2;D='R6'}$test3 = [pscustomobject]@{B=1;E='R7'},    [pscustomobject]@{B=2;E='R8'},    [pscustomobject]@{B=3;E='R9'}#first join by common A and B, then join result by common BJoin-Records (Join-Records $test1 $test2) $test3

So I found an Answer which was more suitable and it uses the join-Object function which was defined below:

you can access it at https://github.com/RamblingCookieMonster/PowerShell/blob/master/Join-Object.ps1

All I really had to do was Define my outputs as $A and $B and $C and so on, and just

$Join1=  Join-Object -Left $A -Right $B -LeftJoinProperty Name - RightJoinProperty Name

made $Join2 then 3 so on until I got it all done

$Join2 = Join-Object -Left $Join1 -Right $C -LeftJoinProperty Name -RightJoinProperty Name$Join3 = Join-Object -Left $Join2 -Right $D -LeftJoinProperty Name -RightJoinProperty Name$Join4 = Join-Object -Left $Join3 -Right $E -LeftJoinProperty Name -RightJoinProperty Name

Until I got it all done