Midavalo Midavalo - 2 months ago 8
SQL Question

SQL query check value exists in lookup based on another column value

I have Name/Value pair records in a table and I need to confirm the values exist against a lookup for each Name

KeyVal - Table of NameValue pairs

| MyID1 | MyRecNumber | MyFieldName | MyFieldValue |
|-------|-------------|-------------|--------------|
| 1 | 1 | FirstField | One |
| 2 | 1 | SecondField | Car |
| 3 | 2 | FirstField | Two |
| 4 | 2 | SecondField | Firetruck |
| 5 | 3 | FirstField | Blue |
| 6 | 3 | SecondField | Car |


LookupTable - Table to match Name Values (from KeyVal) with LookupValue (in CheckVals table)

| MyID2 | MyFieldName | LookupName |
|-------|-------------|------------|
| 1 | FirstField | FieldOne |
| 2 | SecondField | FieldTwo |


CheckVals - Table with valid values for each field

| MyID3 | LookupFieldName | LookupValue |
|-------|-----------------|-------------|
| 1 | FieldOne | One |
| 2 | FieldOne | Two |
| 3 | FieldOne | Three |
| 4 | FieldTwo | Car |
| 5 | FieldTwo | Truck |
| 6 | FieldTwo | Bus |


I have a query that will check values against a single name lookup, but am unsure how to make this check all names against the lookup table. In this query it bypasses the
LookupTable
as I specify the lookup value in the query itself.

DECLARE @AttributeName AS VARCHAR(50)
DECLARE @Lookup AS VARCHAR(50)

SET @AttributeName = 'SecondField'
SET @Lookup = 'FieldTwo';

SELECT
MyRecNumber,
MyFieldName,
MyFieldValue
FROM
dbo.KeyVal kv
WHERE
MyFieldName = @AttributeName
AND MyFieldValue NOT IN
(
SELECT
LookupValue
FROM
dbo.CheckVals cv
WHERE cv.LookupFieldName = @Lookup
)


Question: How can I do a lookup against all values in the
KeyVal
table, through the
LookupTable
table, to check if the value in
MyFieldValue
exists in
CheckVals
against the
MyFieldName
and
LookupName
match?

This is what I'm hoping to get - the two rows that have invalid values are returned in the query results

| MyRecNumber | MyFieldName | MyFieldValue |
|-------------|-------------|--------------|
| 2 | SecondField | Firetruck |
| 3 | FirstField | Blue |





Sample Tables

CREATE TABLE [dbo].[KeyVal](
[MyID1] [smallint] IDENTITY(1,1) NOT NULL,
[MyRecNumber] [smallint] NULL,
[MyFieldName] [varchar](50) NULL,
[MyFieldValue] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[LookupTable](
[MyID2] [smallint] IDENTITY(1,1) NOT NULL,
[MyFieldName] [varchar](50) NULL,
[LookupName] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[CheckVals](
[MyID3] [smallint] IDENTITY(1,1) NOT NULL,
[LookupFieldName] [varchar](50) NULL,
[LookupValue] [varchar](50) NULL
) ON [PRIMARY]


Sample Data

INSERT INTO [dbo].[KeyVal]
([MyRecNumber], [MyFieldName], [MyFieldValue])
VALUES
(1, 'FirstField', 'One'),
(1, 'SecondField', 'Car'),
(2, 'FirstField', 'Two'),
(2, 'SecondField', 'Firetruck'),
(3, 'FirstField', 'Blue'),
(3, 'SecondField', 'Car')

INSERT INTO [dbo].[LookupTable]
([MyFieldName], [LookupName])
VALUES
('FirstField', 'FieldOne'),
('SecondField', 'FieldTwo')

INSERT INTO [dbo].[CheckVals]
([LookupFieldName], [LookupValue])
VALUES
('FieldOne', 'One'),
('FieldOne', 'Two'),
('FieldOne', 'Three'),
('FieldTwo', 'Car'),
('FieldTwo', 'Truck'),
('FieldTwo', 'Bus')

Answer

Let me assume that you want the rows in the first table where the values do not match:

select kv.*
from keyval kv left join
     lookuptable lt
     on kv.myfieldname = lt.myfieldname left join
     checkvals cv
     on cv.LookupFieldName = lt.LookupName and
        cv.LookupValue = kv.MyFieldValue
where cv.myid3 is null;