# Array join and group by similar to SQL inner join and group
**URL:** https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462
**Category:** PowerShell Help
**Created:** [July 17, 2024, 5:44pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462 "2024-07-17T17:44:23Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 17, 2024, 5:44pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/1 "2024-07-17T17:44:23Z")
Hello all,
I do not know enough to ask the right questions to do what I need to do. So if this is answered already … apologies.
I have 2 array objects that need to be joined and have the results of 2nd object but with 1st count joined by same value
For example:
```auto
# Active Directory computer export > grouped by organization and count per organization
$group_01 = $computers | ForEach-Object {
[PSCustomObject]@{
'Organization' = $_.Organization
'Computer' = $_.Name
}
} | Group-Object -Property Organization | Sort-Object -Property Count -Descending | Select @{N='Organization'; E={$_.Name}}, Count
<# result
Organization Count
------------ -----
Org_01 3
Org_02 3
Org_03 2
Org_04 2
Org_05 2
Org_06 1
Org_07 1
Org_08 1
Org_09 1
Org_10 1
Org_11 1
Org_12 1
Org_13 1
Org_14 1
Org_15 1
Org_16 1
Org_17 1
Org_18 1
#>
# 10 percent random subset of $computers > grouped by organization and count per organization
$group_02 = $computers_filter | ForEach-Object {
[PSCustomObject]@{
'Organization' = $_.Organization
'Computer' = $_.Name
}
} | Group-Object -Property Organization | Sort-Object -Property Count -Descending | Select @{N='Organization'; E={$_.Name}}, Count
<# result
Organization Count
------------ -----
Org_01 1
Org_02 1
#>
```
What is needed is similar to SQL statement result below:
```auto
SELECT tbl_Group_02.Organization, tbl_Group_02.Count AS Group_02_Count, tbl_Group_01.Count AS Group_01_Count
FROM tbl_Group_02 INNER JOIN tbl_Group_01 ON tbl_Group_02.Organization = tbl_Group_01.Organization;
<# result
Organization Group_02_Count Group_01_Count
Org_01 1 3
Org_02 1 3
#>
```
---
### Author:  [@Olaf](https://forums.powershell.org/u/Olaf)
#### Post date: [July 17, 2024, 8:03pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/2 "2024-07-17T20:03:44Z")
How about providing some sample input data of what you have in your variables `$computers` and `$computers_filter`? (please post a few lines of CSV data formatted as code)
---
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 18, 2024, 1:15pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/3 "2024-07-18T13:15:44Z")
GM Olaf…
Sorry, I thought the noted dataset result would be sufficient but i do understand it is easier to work with a csv. format…
Please note that the computers array may be irrelevant as it does not contain organization. Organization is extracted from computer name by codes from an external list. I made the names up but same concept \> for example:
COMP-AK-00001 has a code of AK in code list = Alaska organization
With that being said I am grouping 2 lists
1. Original export from AD
2. A subset of AD export
It is a report detail for a spreadsheet that allows a user to see what the original count of computers was AND what the new count is from a random 10 percent subset
So, how to merge $group\_01 + $group\_02 and show only results of $group\_02 subset but with the original count of $group\_01 \> which in SQL can be done with a JOIN
Computers
```auto
$computers = Get-ADOrganizationalUnit -Filter { Name -eq "States" } |
%{Get-ADComputer -Filter * -SearchBase $_.DistinguishedName -Properties *} |
Select -First 25 @{N="Computer";E={$_.Name}}
```
$computers output in CSV format
```auto
Computer
--------
COMP-AK-00001
COMP-AK-00002
COMP-AK-00003
COMP-MD-00001
COMP-MD-00002
COMP-MD-00003
COMP-OR-00001
COMP-OR-00002
COMP-NY-00001
COMP-NY-00002
COMP-FL-00001
COMP-FL-00002
COMP-AZ-00001
COMP-GA-00001
COMP-IA-00001
COMP-CA-00001
COMP-MT-00001
COMP-WA-00001
COMP-VA-00001
COMP-NM-00001
COMP-WY-00001
COMP-ME-00001
COMP-LA-00001
COMP-KY-00001
COMP-UT-00001
```
PASCODES
```auto
$csv = import-csv -Path $PSScriptRoot\pascodes.csv
$pascodes = $csv.PASCODE -join "|"
```
$passcodes in CSV format
```auto
Organization,PASCODE
Alaska,AK
Maryland,MD
Oregon,OR
New York,NY
Florida,FL
Arizona,AZ
Georgia,GA
Iowa,IA
Californiia,CA
Montana,MT
Washington,WA
Virginia,VA
New Mexico,NM
Wyoming,WY
Maine,ME
Louisiana,LA
Kentucky,KY
Utah,UT
```
Group 1
```auto
# Grouping of total computers
$group_01 = $computers |
ForEach-Object {
$organization = If($_ -match "($pascodes)") {$csv.where({$_.PASCODE -eq $Matches[0]}).Organization}
[PSCustomObject]@{
'Organization' = $organization
'Computer' = $_.Computer
}
} |
Group-Object -Property Organization |
Sort-Object -Property Count -Descending |
Select-Object @{N='Organization'; E={$_.Name}}, Count, Group
```
$group\_01 in CSV format
```auto
Organization,Count
Alaska,3
Maryland,3
Oregon,2
New York,2
Florida,2
Arizona,1
Georgia,1
Iowa,1
Californiia,1
Montana,1
Washington,1
Virginia,1
New Mexico,1
Wyoming,1
Maine,1
Louisiana,1
Kentucky,1
Utah,1
```
Group 2
```auto
$group_02 = $computers |
ForEach-Object {
$organization = If($_ -match "($pascodes)") {$csv.where({$_.PASCODE -eq $Matches[0]}).Organization}
[PSCustomObject]@{
'Organization' = If($organization.length -gt 0) {$organization} else {"Unknown"}
'Computer' = $_.Computer
}
} | Get-Random -Count ($computers.Count * .10) |
Group-Object -Property Organization |
Sort-Object -Property Count -Descending |
Select @{N='Organization'; E={$_.Name}}, Count, group
```
$group\_02 in CSV format
```auto
Organization,Count
Alaska,1
Maryland,1
```
---
### Author:  [@Olaf](https://forums.powershell.org/u/Olaf)
#### Post date: [July 18, 2024, 1:18pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/4 "2024-07-18T13:18:14Z")
> [@bslintx](#):
>
> but i do understand it is easier to work with a csv. format…
And why don’t you post it as CSV format? 🤷🏼♂️ Please keep in mind that we should be able to copy it and use it to recreate your issue.
Please edit your reply once again and fix the formatting of your sample data.
Thanks in advance.
---
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 18, 2024, 1:24pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/5 "2024-07-18T13:24:26Z")
It seems we are not on the same page as far as a csv format \> I tried to upload in a csv but not allowed…therefore, I tries to use the group as a comma delimited set
Please advise or give me an idea what you are needing because I am not sure what I need to post as all the data is there - thanks!
---
### Author:  [@Olaf](https://forums.powershell.org/u/Olaf)
#### Post date: [July 18, 2024, 1:27pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/6 "2024-07-18T13:27:10Z")
> [@bslintx](#):
>
> It seems we are not on the same page as far as a csv format \> I tried to upload in a csv but not allowed…therefore, I tries to use the group as a comma delimited set
> [@Olaf](#):
>
> (please post a few lines of CSV data formatted as code)
Simply post the plain text and format it as code.
---
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 18, 2024, 2:17pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/7 "2024-07-18T14:17:12Z")
edited my reply thanks
---
### Author:  [@Olaf](https://forums.powershell.org/u/Olaf)
#### Post date: [July 18, 2024, 3:26pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/8 "2024-07-18T15:26:21Z")
I’m not completely sure if I got what result you expect. Let’s start with the basics:
Data source:
```auto
$Computers = @'
Computer
COMP-AK-00001
COMP-AK-00002
COMP-AK-00003
COMP-MD-00001
COMP-MD-00002
COMP-MD-00003
COMP-OR-00001
COMP-OR-00002
COMP-NY-00001
COMP-NY-00002
COMP-FL-00001
COMP-FL-00002
COMP-AZ-00001
COMP-GA-00001
COMP-IA-00001
COMP-CA-00001
COMP-MT-00001
COMP-WA-00001
COMP-VA-00001
COMP-NM-00001
COMP-WY-00001
COMP-ME-00001
COMP-LA-00001
COMP-KY-00001
COMP-UT-00001
'@ |
ConvertFrom-Csv
$PassCodes = @'
Organization,PASCODE
Alaska,AK
Maryland,MD
Oregon,OR
New York,NY
Florida,FL
Arizona,AZ
Georgia,GA
Iowa,IA
Californiia,CA
Montana,MT
Washington,WA
Virginia,VA
New Mexico,NM
Wyoming,WY
Maine,ME
Louisiana,LA
Kentucky,KY
Utah,UT
'@ |
ConvertFrom-Csv
```
To be able to use the `PassCodes` as a lookup table we convert the input to a hash table
```auto
$HashTable =
$PassCodes |
Group-Object -Property Pascode -AsHashTable
```
Now we _extract_ the in the names coded passcodes … and since your computernames seems to have a regular structure we can use simple splits:
```auto
$DecodedPassCodes =
$Computers |
Select-Object -Property Computer,
@{Name = 'CodedPassCode'; Expression = {($_.Computer -split '-')[1]}}
```
Finally we use the decoded passcodes to look them up in our hashtable
```auto
$EnrichedDataSet =
$DecodedPassCodes |
Select-Object -Property *,
@{Name = 'Organization'; Expression = {$HashTable[$_.CodedPassCode].Organization}}
```
Now you have all information combined in one data set …
```auto
Computer CodedPassCode Organization
-------- ------------- ------------
COMP-AK-00001 AK Alaska
COMP-AK-00002 AK Alaska
COMP-AK-00003 AK Alaska
COMP-MD-00001 MD Maryland
COMP-MD-00002 MD Maryland
COMP-MD-00003 MD Maryland
COMP-OR-00001 OR Oregon
COMP-OR-00002 OR Oregon
COMP-NY-00001 NY New York
COMP-NY-00002 NY New York
COMP-FL-00001 FL Florida
COMP-FL-00002 FL Florida
COMP-AZ-00001 AZ Arizona
COMP-GA-00001 GA Georgia
COMP-IA-00001 IA Iowa
COMP-CA-00001 CA Californiia
COMP-MT-00001 MT Montana
COMP-WA-00001 WA Washington
COMP-VA-00001 VA Virginia
COMP-NM-00001 NM New Mexico
COMP-WY-00001 WY Wyoming
COMP-ME-00001 ME Maine
COMP-LA-00001 LA Louisiana
COMP-KY-00001 KY Kentucky
COMP-UT-00001 UT Utah
```
… and you can group it for whatever property you like. For example by Organization:
```auto
$EnrichedDataSet |
Group-Object -Property Organization
```
… what gives you this:
```auto
Count Name Group
----- ---- -----
3 Alaska {@{Computer=COMP-AK-00001; CodedPassCode=AK; Organization=Alaska}, @{Computer=COMP-AK-00002; CodedPassCode=AK; Organization=Alaska}, @{Computer=COMP-AK-00003; CodedPassCode=AK; Organization=Alaska}}
1 Arizona {@{Computer=COMP-AZ-00001; CodedPassCode=AZ; Organization=Arizona}}
1 Californiia {@{Computer=COMP-CA-00001; CodedPassCode=CA; Organization=Californiia}}
2 Florida {@{Computer=COMP-FL-00001; CodedPassCode=FL; Organization=Florida}, @{Computer=COMP-FL-00002; CodedPassCode=FL; Organization=Florida}}
1 Georgia {@{Computer=COMP-GA-00001; CodedPassCode=GA; Organization=Georgia}}
1 Iowa {@{Computer=COMP-IA-00001; CodedPassCode=IA; Organization=Iowa}}
1 Kentucky {@{Computer=COMP-KY-00001; CodedPassCode=KY; Organization=Kentucky}}
1 Louisiana {@{Computer=COMP-LA-00001; CodedPassCode=LA; Organization=Louisiana}}
1 Maine {@{Computer=COMP-ME-00001; CodedPassCode=ME; Organization=Maine}}
3 Maryland {@{Computer=COMP-MD-00001; CodedPassCode=MD; Organization=Maryland}, @{Computer=COMP-MD-00002; CodedPassCode=MD; Organization=Maryland}, @{Computer=COMP-MD-00003; CodedPassCode=MD; Organization=Maryland}}
1 Montana {@{Computer=COMP-MT-00001; CodedPassCode=MT; Organization=Montana}}
1 New Mexico {@{Computer=COMP-NM-00001; CodedPassCode=NM; Organization=New Mexico}}
2 New York {@{Computer=COMP-NY-00001; CodedPassCode=NY; Organization=New York}, @{Computer=COMP-NY-00002; CodedPassCode=NY; Organization=New York}}
2 Oregon {@{Computer=COMP-OR-00001; CodedPassCode=OR; Organization=Oregon}, @{Computer=COMP-OR-00002; CodedPassCode=OR; Organization=Oregon}}
1 Utah {@{Computer=COMP-UT-00001; CodedPassCode=UT; Organization=Utah}}
1 Virginia {@{Computer=COMP-VA-00001; CodedPassCode=VA; Organization=Virginia}}
1 Washington {@{Computer=COMP-WA-00001; CodedPassCode=WA; Organization=Washington}}
1 Wyoming {@{Computer=COMP-WY-00001; CodedPassCode=WY; Organization=Wyoming}}
```
… if that’s not what you was looking for you have to elaborate more detailed.
---
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 18, 2024, 5:13pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/9 "2024-07-18T17:13:32Z")
Hey Olaf…I appreciate the try.
I am trying to join $group\_01 and $group\_02 on “Organization” … the results should be $group\_02 but with $group\_01 original Count
no need to concern with how to get pascodes in as i have that with the where object…but i cannot figure out how to display $group\_02 list with $group\_01 Count included
I have JoinModule installed so I can use this if easier to get the results needed
So final result should look like this:
pseudocode ----- Join $group\_02 with $group\_01 by Organization \> Select Organization, Group\_02\_Count, Group\_01\_Count
```auto
Organization, Group_01_Count, Group_02_Count
Alaska,3,1
Maryland,3,1
```
since i have the pascode figured out using where from an imported csv … just need to figure out $group\_01 + $ $group\_02 join
Thanks for the patience
>
---
### Author:  [@Olaf](https://forums.powershell.org/u/Olaf)
#### Post date: [July 18, 2024, 5:24pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/10 "2024-07-18T17:24:20Z")
> [@bslintx](#):
>
> I am trying to join $group\_01 and $group\_02 on “Organization” … the results should be $group\_02 but with $group\_01 original Count
What is `$group_01` and what is `$group_02`? 🤷🏼♂️
---
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 18, 2024, 6:05pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/11 "2024-07-18T18:05:21Z")
They are the groups (arrays) i have demonstrated in the code blocks in previous posts \> see above 👆
But I digress ----- to make it to where you do not have to weed through that code again I will make 2 very small datasets that i need to merge and display in a specific format
How do i join this dataset …
Group 1 dataset
```auto
Organization,Count
Alaska, 3
Maryland, 3
Oregon, 2
New York, 2
Florida, 2
Arizona, 1
```
…with this dataset
Group 2 dataset
```auto
Alaska, 1
Maryland, 1
```
and have the merge look like this dataset?..
```auto
Organization, Group_1_Dataset_Count, Group_2_dataset_Count
Alaska, 3, 1
Maryland, 3, 1
```
Note how the result will only have the organizations of where Group\_2\_dataset organization is equal to Group\_1\_dataset organization … but will include Group\_1\_dataset count
---
### Author:  [@bslintx](https://forums.powershell.org/u/bslintx)
#### Post date: [July 18, 2024, 8:54pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/12 "2024-07-18T20:54:37Z")
I ended up using the JoinModule as I am more familiar with SQL than arrays. The following gives me the intended result. I had to break out the counts in the select statements to make it work.
Once I did that then the following one-liner joined to the final recordset:
_$group\_02 |Join $group\_01 -On Organization_
```auto
$csv = import-csv -Path $PSScriptRoot\pascodes.csv
$pascodes = $csv.PASCODE -join "|"
$computers = Get-ADOrganizationalUnit -Filter { Name -eq "Computer OU" } |
%{Get-ADComputer -Filter * -SearchBase $_.DistinguishedName -Properties *} |
Select @{N="Computer";E={$_.Name}}
$group_01 = $computers |
ForEach-Object {
$organization = $organization = If($_ -match "($pascodes)") {$csv.where({$_.PASCODE -eq $Matches[0]}).Organization}
[PSCustomObject]@{
'Organization' = If($organization.length -gt 0) {$organization} else {"Unknown"}
'Computer' = $_.Computer
}
} |
Group-Object -Property Organization |
Sort-Object -Property Count -Descending |
Select @{N='Organization'; E={$_.Name}},
@{N='Group_01_Count'; E={$_.Count}}
$group_02 = $computers |
ForEach-Object {
$organization = If($_ -match "($pascodes)") {$csv.where({$_.PASCODE -eq $Matches[0]}).Organization}
[PSCustomObject]@{
Organization = If($organization.length -gt 0) {$organization} else {"Unknown"}
Computer = $_.Computer
}
} | Get-Random -Count ($computers.Count * .10) |
Group-Object -Property Organization |
Sort-Object -Property Count -Descending |
Select @{N='Organization'; E={$_.Name}},
@{N='Group_02_Count'; E={$_.Count}}
$group_02 |Join $group_01 -On Organization
```
---
### Author:  [@Olaf](https://forums.powershell.org/u/Olaf)
#### Post date: [July 18, 2024, 8:58pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/13 "2024-07-18T20:58:20Z")
> [@bslintx](#):
>
> The following gives me the intended result.
Hey hey … great to hear …
… and thanks for sharing. 👍🏼 🤟🏼
---
### Author:  [@system](https://forums.powershell.org/u/system)
#### Post date: [August 17, 2024, 8:58pm UTC](https://forums.powershell.org/t/array-join-and-group-by-similar-to-sql-inner-join-and-group/24462/14 "2024-08-17T20:58:37Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.