title: Join SQL query Results and Get-ChildItem Results
body: <p><strong>Background:</strong> I have a directory with a number of files that are imported to SQL server. </p>
  
  <p><strong>Task:</strong> Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query.</p>
  
  <p><strong>Ultimate objective:</strong> To display SQL results besides the filenames but the resultset being displayed should also show files having no entries in SQL server. Something like <code>RIGHT JOIN</code> in SQL server queries.</p>
  
  <p><strong>Powershell Code</strong></p>
  
  <pre class="lang-Powershell prettyprint-override"><code>$files = Get-ChildItem -Recurse -Force $filePath -ErrorAction SilentlyContinue | Where-Object { ($_.PSIsContainer -eq $false) } | Select-Object Name
  $Server = "Loadv1"
  $DB = "LoadDB"
  $dbResults = @()
  
  ForEach ($file in $files)
  {
      $fileName = $file.name
      write-host $fileName
  
      if($fileName.Length -gt 1)
      {
          $Query = "
              SELECT FileName,CurrentStatus 
              FROM LogStatus
              WHERE FileName LIKE '$fileName%'
          "
  #       Write-host $Query
      }
  
      $dbResults += Invoke-Sqlcmd -ServerInstance $Server -Database $DB -Query $Query
  }
  $dispResults = $dbResults,$file
  $dispResults | Format-Table -autosize
  </code></pre>
  
  
  
  <p><strong>Work done so far:</strong> I have been able to fetch the file names using Get-ChildItem and loop them to get the query results. However, the result I am currently getting does not show the files that don't have corresponding entry in SQL server table</p>
  
  <p>Current Result</p>
  
  <pre><code>OperationalLanding20150622061502.dat
  OperationalLandingAudit20150622061502.dat
  OperativeThird_Party_System20150616090701.dat
  
  
  FileName                                       CurrentStatus
  OperationalLandingAudit20150622061502.dat      SSIS Package Complete
  OperativeThird_Party_System20150616090701.dat  SSIS Package Complete
  </code></pre>
  
  <p>Expected Result</p>
  
  <pre><code>OperationalLanding20150622061502.dat
  OperationalLandingAudit20150622061502.dat
  OperativeThird_Party_System20150616090701.dat
  
  
  FileName                                       CurrentStatus
  OperationalLanding20150622061502.dat           NULL
  OperationalLandingAudit20150622061502.dat      SSIS Package Complete
  OperativeThird_Party_System20150616090701.dat  SSIS Package Complete
  </code></pre>
  
  <p>Hoping I was able to explain my requirement above.</p>