title: Doing a join only if count is greater than one
body: <p>I wonder if the following a bit contrived example is possible without using intermediary variables and a conditional clause.</p>
  
  <p>Consider an intermediary query which can produce a result set that contain either no rows, one row or multiple rows. Most of the time it produces just one row, but when multiple rows, one should join the resulting rows to another table to prune it down to either one or no rows. After this if there is one row (as opposed to no rows), one would want to return multiple columns as produced by the original intermediary query.</p>
  
  <p>I have in my mind something like following, but it won't obviously work (multiple columns in switch-case, no join etc.), but maybe it illustrates the point. What I would like to have is to just return what is currently in the <code>SELECT</code> clause in case <code>@@ROWCOUNT = 1</code> or in case it is greater, do a <code>INNER JOIN</code> to <code>Auxilliary</code>, which prunes down <code>x</code> to either one row or no rows and then return that. I don't want to search <code>Main</code> more than once and <code>Auxilliary</code> only when <code>x</code> here contains more than one row.</p>
  
  <pre><code>SELECT x.MainId, x.Data1, x.Data2, x.Data3,
  CASE         
       WHEN @@ROWCOUNT IS NOT NULL AND @@ROWCOUNT = 1 THEN
          1
       WHEN @@ROWCOUNT IS NOT NULL AND @@ROWCOUNT &gt; 1 THEN
          -- Use here @id or MainId to join to Auxilliary and there 
          -- FilteringCondition = @filteringCondition to prune x to either 
          -- one or zero rows.
  END         
  FROM
  (
      SELECT
          MainId,
          Data1,
          Data2,
          Data3
      FROM Main
      WHERE
          MainId = @id
  ) AS x;
  
  CREATE TABLE Main
  (
      -- This Id may introduce more than row, so it is joined to
      -- Auxilliary for further pruning with the given conditions.
      MainId INT,
      Data1 NVARCHAR(MAX) NOT NULL,
      Data2 NVARCHAR(MAX) NOT NULL,
      Data3 NVARCHAR(MAX) NOT NULL,
  
      AuxilliaryId INT NOT NULL
  );
  
  
  CREATE TABLE Auxilliary
  (
      AuxilliaryId INT IDENTITY(1, 1) PRIMARY KEY,
      FilteringCondition NVARCHAR(1000) NOT NULL
  );
  </code></pre>
  
  <p>Would this be possible in one query without a temporary table variable and a conditional? Without using a CTE?</p>
  
  <p>Some sample data would be</p>
  
  <pre><code>INSERT INTO Auxilliary(FilteringCondition)
  VALUES
      (N'SomeFilteringCondition1'),
      (N'SomeFilteringCondition2'),
      (N'SomeFilteringCondition3');
  
  
  INSERT INTO Main(MainId, Data1, Data2, Data3, AuxilliaryId)
  VALUES
      (1, N'SomeMainData11', N'SomeMainData12', N'SomeMainData13', 1),
      (1, N'SomeMainData21', N'SomeMainData22', N'SomeMainData23', 2),
      (2, N'SomeMainData31', N'SomeMainData32', N'SomeMainData33', 3);
  </code></pre>
  
  <p>And a sample query, which actually behaves as I'd like it to behave with the caveat I'd want to do the join only if querying <code>Main</code> directly with the given ID produces more than one result.</p>
  
  <pre><code>DECLARE @id AS INT = 1;
  DECLARE @filteringCondition AS NVARCHAR(1000) = N'SomeFilteringCondition1';
  
  SELECT *
  FROM
      Main
      INNER JOIN Auxilliary AS aux ON aux.AuxilliaryId = Main.AuxilliaryId
  WHERE MainId = @id AND aux.FilteringCondition = @filteringCondition;
  </code></pre>