Create a subquery with a filter and then left outer join other tables

• JeremyU

  SSChasing Mays

  Points: 640

  More actions

  March 10, 2022 at 12:27 am

  #3996517

  I have such a situation. I need to create a subquery and set to Type = 1.

  While I have a left join always pulling from FCSTPERFSTATIC, I need to create a sub query first to pull type 1 out of the HISTWIDE table and then do the left join.

  . When I remove the Type = 1 in the where statement you see below example where an item had forecast but no sales. We would want to see those.

  When I filter to type = 1 in the main query that record won’t be returned in the data

  SELECT f.IDUNIT
  ,l.Location
  ,i.Description
  ,d.Information
  ,f.Date
  ,a.Actuals
  ,f.Forecast
  FROM Forecast f
  LEFT OUTER JOIN Information a
  ON f.IDUNIT=a.IDUNIT
  and a.[TYPE]='1'
  

   

  I wanted to ask if someone may have an idea how to create a subquery and then left outer join.

  When I just type ,(SELECT TYPE FROM Information a WHERE TYPE='1') AS 'Type'

  in SELECT statement I receive an error

  `

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  `

   

  • This topic was modified 2 years, 8 months ago by  JeremyU.
  • This topic was modified 2 years, 8 months ago by  JeremyU.

• Ken McKelvey

  SSCoach

  Points: 18895

  More actions

  March 10, 2022 at 9:43 am

  #3996786

  You either need to filter in the JOIN or do something like WHERE (a.Type = 1 OR a.Type IS NULL)

  https://sqlbolt.com/lesson/select_queries_order_of_execution