title: TSQL Conditional Join Statement?
body: <p>I am working on a dynamic query that allows me to define field types in a database table and then pull a list of user defined fields that were selected during the creation of a dashboard along with the values that were chosen for each of the logic operators.</p>
  
  <p>For example, use can choose <code>field1</code> which is <code>employeeID</code>. They can then provide the ID they want to search by and it will store the <code>fieldID</code> and <code>fieldValue</code> in a table for when we need to render the data.</p>
  
  <p>Now depending on the field that was chosen, I may need to join up another table to get further information such as the employees name.</p>
  
  <p>While this is simple enough, the tricky part comes into play when having to join the value against multiple possible tables. If I stored an <code>employeeID</code>, I would join that field with the employee table. However, if I stored a <code>storeID</code>, I would need to join that field with the stores table instead.</p>
  
  <p>Here was my thinking before I got to the end and realized I was stuck. Basically, I was going to say "if this field was a people table <code>isPeople=1</code> then join our <code>employeeTable</code>. </p>
  
  <p>If this field <code>isTool=1</code>, join our tools table.</p>
  
  <p>I essentially have a <code>fieldType</code> and a <code>fieldValue</code> and based on the type, I need to join a specific table on the value I have stored.</p>
  
  <p><a href="https://i.stack.imgur.com/pPwgF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pPwgF.png" alt="enter image description here"></a></p>
  
  <p>As I was working through this making it dynamic sql, I realized that I couldn't use the <code>fl.*</code> fields to create my <code>IF</code> conditions.</p>
  
  <p>ie. <code>IF (fl.isPeople =1) BEGIN ... JOIN Employee Table ... END</code></p>
  
  <p>I thought taking the dynamic approach would have been the only way to do this but now I can't think of a solution to my problem.</p>
  
  <p><strong>In Conclusion...</strong></p>
  
  <p>I am trying to join tables and query fields based on a condition. Since I couldn't seem to do that in my normal stored procedure, I tried to do it dynamically and ended up here and this very confusion problem I am trying to explain.</p>
  
  <p>Let me know if I can further clarify.</p>
  
  <p><strong>Update:</strong> </p>
  
  <pre><code>USE [red]
  GO
  /****** Object:  StoredProcedure [dbo].[ti2_fetch_dashboard]    Script Date: 9/15/2016 9:55:39 PM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:      Carl
  -- Create date: Sept 13, 2016
  -- Description: Fetch Single Dashboard for Editing
  -- =============================================
  ALTER PROCEDURE [dbo].[ti2_fetch_dashboard_test]
  @dashboardID INT, @SQL NVARCHAR(MAX) = ''
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Fetch our dashboard
      SELECT @SQL = 'SELECT d.dashboardID,
             d.dashboardOwner,
             e.PreferredName AS ownerFirst,
             e.LastName AS ownerLast,
             e.NTID AS ownerNTID,
             d.dashboardTitle,
             d.dashboardDescription,
             d.dashboardType,
             d.creator,
             d.timestamp,
             d.lastUpdate,
             d.isDeleted,
             (SELECT sub.QID AS subscriberQID,
                     e.PreferredName AS subscriberFirst,
                     e.LastName AS subscriberLast,
                     e.NTID AS subscriberNTID
              FROM   dbo.ti_dashboard_subscribers AS sub
                     LEFT OUTER JOIN
                     dbo.EmployeeTable AS e
                     ON sub.QID = e.QID
              WHERE  sub.dashboardID = d.dashboardID
              FOR    XML PATH (''options''), ELEMENTS, TYPE, ROOT (''subscribers'')),
             (SELECT   f.fid,
                       f.fieldID,
                       f.[order],
                       cfg.primaryAllowed,
                       cfg.secondaryAllowed,
                       cfg.exportAllowed,
                       mf.fieldName,
                       mf.placeholder
              FROM     dbo.ti_dashboards_fields AS f
                       INNER JOIN
                       dbo.ti_dashboard_field_types AS ft
                       ON f.typeID = ft.fieldTypeID
                       INNER JOIN
                       dbo.ti_dashboard_master_fields_config AS cfg
                       ON f.fieldID = cfg.fieldID
                       INNER JOIN
                       dbo.ti_dashboards_master_fields AS mf
                       ON mf.fieldID = f.fieldID
              WHERE    f.dashboardID = d.dashboardID
                       AND ft.fieldTypeName = ''Primary''
              ORDER BY f.[order] ASC
              FOR      XML PATH (''fields''), ELEMENTS, TYPE, ROOT (''primaryFields'')),
             (SELECT   f.fid,
                       f.fieldID,
                       f.[order],
                       cfg.primaryAllowed,
                       cfg.secondaryAllowed,
                       cfg.exportAllowed,
                       mf.fieldName,
                       mf.placeholder
              FROM     dbo.ti_dashboards_fields AS f
                       INNER JOIN
                       dbo.ti_dashboard_field_types AS ft
                       ON f.typeID = ft.fieldTypeID
                       INNER JOIN
                       dbo.ti_dashboard_master_fields_config AS cfg
                       ON f.fieldID = cfg.fieldID
                       INNER JOIN
                       dbo.ti_dashboards_master_fields AS mf
                       ON mf.fieldID = f.fieldID
              WHERE    f.dashboardID = d.dashboardID
                       AND ft.fieldTypeName = ''Secondary''
              ORDER BY f.[order] ASC
              FOR      XML PATH (''fields''), ELEMENTS, TYPE, ROOT (''secondaryFields'')),
             (SELECT   f.fid,
                       f.fieldID,
                       f.[order],
                       cfg.primaryAllowed,
                       cfg.secondaryAllowed,
                       cfg.exportAllowed,
                       mf.fieldName,
                       mf.placeholder
              FROM     dbo.ti_dashboards_fields AS f
                       INNER JOIN
                       dbo.ti_dashboard_field_types AS ft
                       ON f.typeID = ft.fieldTypeID
                       INNER JOIN
                       dbo.ti_dashboard_master_fields_config AS cfg
                       ON f.fieldID = cfg.fieldID
                       INNER JOIN
                       dbo.ti_dashboards_master_fields AS mf
                       ON mf.fieldID = f.fieldID
              WHERE    f.dashboardID = d.dashboardID
                       AND ft.fieldTypeName = ''Export''
              ORDER BY f.[order] ASC
              FOR      XML PATH (''fields''), ELEMENTS, TYPE, ROOT (''exportFields'')),
              (SELECT   l.[fieldID],
                      -- Get the details of each of those fields
                       (SELECT fl.queryField,
                               fl.allowMultiple,
                               fl.isPeople,
                               fl.isDate,
                               fl.isSelect,
                               fl.isInput,
                               fl.isTool,
                               fl.selectOptions,
                               -- Get the values associcated with the fields
                               (SELECT dv.value';
  
                               -- If fl.isPeople = 1
                               SELECT @SQL += ', e.PreferredName AS FirstName, e.LastName, e.NTID, e.QID ';
  
                               -- If fl.isTool = 1
                               SELECT @SQL += ', t.toolName, t.toolType ';
  
                               -- Primary Table
                               SELECT @SQL += 'FROM dbo.ti_dashboards_logic AS dv ';
  
                               -- If fl.isPeople = 1
                               SELECT @SQL += 'LEFT OUTER JOIN dbo.EmployeeTable AS e ON dv.value = e.QID ';
  
                               -- If fl.isTool = 1
                               SELECT @SQL += 'LEFT OUTER JOIN ti_tools AS t ON dv.value = t.tool ';
  
                               -- Rest of Statement
                               SELECT @SQL += 'WHERE  dv.fieldID = l.fieldID
                                       AND dv.dashboardID = d.dashboardID
                                FOR    XML PATH (''data''), TYPE, ELEMENTS, ROOT (''values''))
                        FROM   dbo.ti_dashboard_master_fields_config AS fl
                        WHERE  fl.fieldID = l.fieldID
                               AND l.dashboardID = d.dashboardID
                        FOR    XML PATH (''fields''), TYPE, ELEMENTS, ROOT (''logicMeta''))
              FROM     [red].[dbo].[ti_dashboards_logic] AS l
              WHERE    l.dashboardID = d.dashboardID
              GROUP BY l.fieldID, l.dashboardID
              FOR      XML PATH (''fields''), TYPE, ELEMENTS, ROOT (''logicFields'')),
              (SELECT sdb.dashboardID ,
                         sdb.fieldID ,
                         sdb.sort ,
                         sdb.[order] ,
                         mf.fieldName
                  FROM dbo.ti_dashboards_sorting AS sdb
                  JOIN dbo.ti_dashboard_master_fields_config AS sf
                  ON sdb.fieldID = sf.fieldID
                  JOIN dbo.ti_dashboards_master_fields AS mf
                  ON sf.fieldID = mf.fieldID
                  WHERE sdb.dashboardID = d.dashboardID
                  ORDER BY sdb.[order] ASC
                  FOR    XML PATH (''fields''), TYPE, ELEMENTS, ROOT (''sortingFields'')
              )
      FROM   dbo.ti_dashboards AS d
             LEFT OUTER JOIN
             dbo.EmployeeTable AS e
             ON d.dashboardOwner = e.QID
      WHERE  d.dashboardID = @_dashboardID
             AND d.isDeleted = 0
      FOR    XML PATH (''data''), ELEMENTS, TYPE, ROOT (''root'')';
  
      EXEC sp_executesql @SQL, N'@_dashboardID INT', @_dashboardID = @dashboardID
  
  END
  </code></pre>