Get values from a SQL Query to use with an IN clause

• Jeffs1977

  SSC-Addicted

  Points: 487

  More actions

  May 16, 2023 at 3:21 pm

  #4190835

  My Query gives me a list of GUID's that I would like to use to help create a single returned result with the values from my query within an IN clause.

  My Query

  SELECT DISTINCT 
   t.Field_2_Id AS EncounterId
  INTO #temp
  FROM BillingEncounter.[Procedure]
  RIGHT JOIN (SELECT * FROM [CosmosDirect].[Missing_Id] WHERE TableName =  'BillingEncounter.Procedure') t ON t.Field_1_Id = BillingProcedureId AND t.Field_2_Id = EncounterId
  WHERE BillingProcedureId IS NULL

  SELECT * FROM #Temp

  Results

  4410f746-b95f-4746-8de6-b61e3d3d569h

  6eae0bea-f4fa-4577-96e0-747ee887b0dr

  74a8685f-c641-46ec-a039-55a9b9cfea93

  9e914ac8-9226-493f-aa06-ca458b4111f4

  cd05f2b0-2545-49d5-a57c-35702cb4f715

  ef042840-c76e-4177-8222-be1f6eaf7750

  I would like to take the results from this query to write something like this, so I can paste this in Cosmos (it hardcodes everything to the IN and uses my values from my query to put into the IN clause.

  WHERE contains(c.partitionKey, "BillingEncounter/17792") and c.encounterId IN

  (

  '4410f746-b95f-4746-8de6-b61e3d3d569h',

  '6eae0bea-f4fa-4577-96e0-747ee887b0dr',

  '74a8685f-c641-46ec-a039-55a9b9cfea93',

  '9e914ac8-9226-493f-aa06-ca458b4111f4',

  'cd05f2b0-2545-49d5-a57c-35702cb4f715',

  'ef042840-c76e-4177-8222-be1f6eaf7750'

  )

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  May 16, 2023 at 3:40 pm

  #4190867

  As you already have the results in #temp, the query can be written as follows

  WHERE contains(c.partitionKey, "BillingEncounter/17792") 
  AND EXISTS (SELECT 1 FROM #temp t where t.EncounterId = c.EncounterId)

  ---