for-each-object inside for-each-object

Question

I'm writing loop inside loop and use main loop variable into child loop array variable.

input parameters

param(
[array]$Emp_ID=@(1,2),
[array]$Emp_Name=@("Scott", "Jones")
)

$Sql_Get_Emp_ID_List = @()
    $Emp_ID | ForEach-Object { $Src_Emp_ID = $_
    $Emp_Name | ForEach-Object { $Src_Emp_Name = $_

    $Query_GetDeptID="(Select Dept_ID From Dept_Table Where Emp_Name = '$Src_Emp_Name' And Emp_ID = $Src_Emp_ID)"        
    }
    }

I'm getting 4 cross join sql statements out of above for-each-object loop. below are the statements but I want to get only 2 statements.

Select Dept_ID From Dept_Table Where Emp_Name = 'Scott' And Emp_ID = 1
Select Dept_ID From Dept_Table Where Emp_Name = 'Scott' And Emp_ID = 2
Select Dept_ID From Dept_Table Where Emp_Name = 'Jones' And Emp_ID = 1
Select Dept_ID From Dept_Table Where Emp_Name = 'Jones' And Emp_ID = 2

I want get output like this:

Select Dept_ID From Dept_Table Where Emp_Name = 'Scott' And Emp_ID = 1
Select Dept_ID From Dept_Table Where Emp_Name = 'Jones' And Emp_ID = 2

Answer 1

If you want to iterate over two equal length arrays, you can use one Foreach-Object and one index for both arrays.

$emp_id = 1,2
$emp_name = 'scott','jones'
$query = "Select Dept_ID From Dept_Table Where Emp_Name = '{0}' And Emp_ID = {1}"

0..($emp_id.Count-1) | Foreach-Object { $query -f $emp_name[$_],$emp_id[$_] }

Explanation:

$emp_id.Count returns the number of items (2) in the $emp_id array. Since indexes start at 0, you will only need indexes [0] and [1] here. $query is your query string with placeholders {0} and {1} for the string format operator (-f). Then the Foreach-Object creates a query for each index substituting one element (the same element) from each array.