Linked Questions

Popular Questions

Custom Table that joins fields back to master table

Asked by At

I have the following table below that stores which table/fields are required to have a value to meet Eligibility.

Basically Client.Field1 has to contain a value of 1, Client.Field2 a value of 2, etc.

Does anyone know how I return True if all the values match within the Client table or False if the values don't match in the Client table, and it be flexible enough that if an extra field was added it would be picked up?

CREATE TABLE [CustomField](
    [CustomFieldID] [smallint] IDENTITY(1,1) NOT NULL,
    [FieldTable] [nvarchar](100) NOT NULL,
    [FieldName] [nvarchar](100) NOT NULL,
    [EligibilityValue] [tinyint] NULL,
 CONSTRAINT [PK_CustomField] PRIMARY KEY CLUSTERED 
(
    [CustomFieldID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
INSERT [CustomField] ([CustomFieldID], [FieldTable], [FieldName], [EligibilityValue]) VALUES (119, N'Client', N'Field1', 1)
GO
INSERT [CustomField] ([CustomFieldID], [FieldTable], [FieldName], [EligibilityValue]) VALUES (120, N'Client', N'Field2', 2)
GO
INSERT [CustomField] ([CustomFieldID], [FieldTable], [FieldName], [EligibilityValue]) VALUES (130, N'Client', N'Field3', 1)
GO
INSERT [CustomField] ([CustomFieldID], [FieldTable], [FieldName], [EligibilityValue]) VALUES (141, N'Client', N'Field4', 2)
GO

CREATE TABLE Client
(
    ClientID int,
    Field1 int,
    Field2 int,
    Field3 int,
    Field4 int
)

--this would return True when the comparison is done
insert into Client
(
    ClientID,
    Field1,
    Field2,
    Field3,
    Field4
)
values
(
    100,
    1,
    2,
    1,
    2
)

--this would return False when the comparison is done
insert into Client
(
    ClientID,
    Field1,
    Field2,
    Field3,
    Field4
)
values
(
    101,
    1,
    2,
    2,
    2
)

Related Questions