/*
I'm having a problem making a select statement to return the
correct results.
Here is an example of what I'm trying to do. Two tables:
@TblNameParts which contains the master list of all the name
parts and the MasterNameID which is a pointer to the master
record.
@TblCriteria which is based on the parts of the name being
searched for.
I need a select statement which will return a list of the
valid MasterNameIDs. I know there is a simple solution
I'm just having a hard time finding it.
The logic is:
1) All @TblCriteria.PartValue's must be in @TblNameParts.PartValue
grouped by the @TblNameParts.MasterNameID.
2) @TblCriteria.PartValue's can be in any order in @TblNameParts.
3) A @TblNameParts.PartValue can only be used once for a
@TblCriteria.PartValue. This is where I'm running into
trouble. You should be able to glean what I mean by the
example's correct results below.
I really appreciate any assistance you can provide, thanks.
*/
SET NOCOUNT ON
-- Main master name parts table.
DECLARE @TblNameParts TABLE (
MasterNameID int,
PartIndex int,
PartValue varchar(50)
)
-- MasterNameID 10: 'Stacy Smith'
INSERT INTO @TblNameParts VALUES (10, 0, 'Stacy')
INSERT INTO @TblNameParts VALUES (10, 1, 'Smith')
-- MasterNameID 15: 'John Doe'
INSERT INTO @TblNameParts VALUES (15, 0, 'John')
INSERT INTO @TblNameParts VALUES (15, 1, 'Doe')
-- MasterNameID 20: 'Stacy Ann Smith'
INSERT INTO @TblNameParts VALUES (20, 0, 'Stacy')
INSERT INTO @TblNameParts VALUES (20, 1, 'Ann')
INSERT INTO @TblNameParts VALUES (20, 2, 'Smith')
-- MasterNameID 25: 'Stacy Stacy'
INSERT INTO @TblNameParts VALUES (25, 0, 'Stacy')
INSERT INTO @TblNameParts VALUES (25, 1, 'Stacy')
-- MasterNameID 30: 'Smith Stacy'
INSERT INTO @TblNameParts VALUES (30, 0, 'Smith')
INSERT INTO @TblNameParts VALUES (30, 1, 'Stacy')
-- Criteria to find a master name ID.
DECLARE @TblCriteria TABLE (
CriteriaID int,
PartValue varchar(50)
)
-- Search 1 Criteria: 'John Doe'
INSERT INTO @TblCriteria VALUES (0, 'John')
INSERT INTO @TblCriteria VALUES (1, 'Doe')
-- Search 1 Correct Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
JOIN @TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
-- Reset Criteria.
DELETE
FROM @TblCriteria
-- Search 2 Criteria: 'Stacy'
INSERT INTO @TblCriteria VALUES (0, 'Stacy')
-- Search 2 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
JOIN @TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @TblCriteria
-- Search 3 Criteria: 'Stacy Smith'
INSERT INTO @TblCriteria VALUES (0, 'Stacy')
INSERT INTO @TblCriteria VALUES (1, 'Smith')
-- Search 3 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
JOIN @TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @TblCriteria
-- Search 4 Criteria: 'Stacy Stacy'
INSERT INTO @TblCriteria VALUES (0, 'Stacy')
INSERT INTO @TblCriteria VALUES (1, 'Stacy')
-- Search 4 Correct Result:
-- MasterNameID 25
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
JOIN @TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 25
SET NOCOUNT OFF
