I am calling this Part I because I know I still have some more details to discover about something I learned today. Not sure I fully understand it right now, but by the time I get to Part N, I am confident I will know way to much about the subject. I am not going to spill all the details out right now, I want you to think about what I am going to present and see if you come to the same conclusion that I did.

Here is the script for the tables and data that I am using for my examples.

USE [tempdb]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testTable1]') AND type in (N'U'))
DROP TABLE [dbo].[testTable1]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testTable2]') AND type in (N'U'))
DROP TABLE [dbo].[testTable2]
GO

CREATE TABLE [dbo].[testTable1](
[ID] [int] NOT NULL,
[LookupValue] [varchar](50) NOT NULL)
GO

CREATE TABLE [dbo].[testTable2](
[ID] [int] NOT NULL,
[LookupType] [varchar](50) NOT NULL)
GO

INSERT INTO [testTable1] VALUES (1, '1')
INSERT INTO [testTable1] VALUES (2, '1/1/2007 12:00:00 AM')
INSERT INTO [testTable1] VALUES (3, 'Test Data 1')
INSERT INTO [testTable1] VALUES (1, '2')
INSERT INTO [testTable1] VALUES (2, '2/1/2008 12:00:00 AM')
INSERT INTO [testTable1] VALUES (3, 'Test Data 2')
INSERT INTO [testTable1] VALUES (1, '3')
INSERT INTO [testTable1] VALUES (2, '3/1/2009 12:00:00 AM')
INSERT INTO [testTable1] VALUES (3, 'Test Data 3')
GO

DECLARE @count INT
SET @count = 0
    WHILE @count < 10
    BEGIN
    INSERT INTO [testTable1] SELECT * FROM [testTable1]
    SET @count = @count + 1
    END
GO

SELECT COUNT(*) FROM [testTable1]
INSERT INTO [testTable2] VALUES (1 , 'Integer')
INSERT INTO [testTable2] VALUES (2 , 'Date')
INSERT INTO [testTable2] VALUES (3 , 'Text')
GO

Now executing a simple query, I get the results I was expecting.

--Simple Query

SELECT

      a.[ID] AS LookupValueID

      ,b.[ID] AS LookupTypeID

      ,a.[LookupValue]

      ,b.[LookupType]

FROM

      [dbo].[testTable1] a INNER JOIN

      [dbo].[testTable2] b ON a.[ID] = b.[ID]

LookupValueID LookupTypeID LookupValue                                        LookupType

------------- ------------ -------------------------------------------------- ------------------

1             1            1                                                  Integer

2             2            1/1/2007 12:00:00 AM                               Date

3             3            Test Data 1                                        Text

1             1            2                                                  Integer

2             2            2/1/2008 12:00:00 AM                               Date

3             3            Test Data 2                                        Text

1             1            3                                                  Integer

2             2            3/1/2009 12:00:00 AM                               Date

3             3            Test Data 3                                        Text

1             1            1                                                  Integer

2             2            1/1/2007 12:00:00 AM                               Date

3             3            Test Data 1                                        Text

1             1            2                                                  Integer

2             2            2/1/2008 12:00:00 AM                               Date

3             3            Test Data 2                                        Text

9216 records found

Now I am adding a filter so that I only return 'Date' values.

--Simple Query 2

SELECT

      a.[ID] AS LookupValueID

      ,b.[ID] AS LookupTypeID

      ,a.[LookupValue]

      ,b.[LookupType]

FROM

      [dbo].[testTable1] a INNER JOIN

      [dbo].[testTable2] b ON

            a.[ID] = b.[ID] AND b.[LookupType] = 'Date'