Name part search. Stumped...

/*
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 OFFGoogle for "relational division". There's a very comprehensive article on th
e
subject by Joe Celko.
ML
http://milambda.blogspot.com/

My own little piece of SQL Wierdness

This has me stumped.
I have a parent and a child table with a constraint on the parent's foreign
key in
the child.
So with this in mind, every once in a while when I am exporting data, I get
an error
that there is a row in the child table with and ID that is not in the parent
table.
One of our programmers is using ADO transactions and beginning it in source
code (not
a proc). Then he adds a row to the parent table, gets the ID in one proc an
d adds a
row to the child table in another proc. Then he commits or aborts when he g
ets back
to code.
Can you think of any reason why the row in the child table is there, but the
row in
the parent isn't there when exporting data?
Thanks.Do you have foreign key constraints on your tables? If so, then it sounds
like a query issue. Otherwise, my guess is the programs arent doing
"something" right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:ut9Jr40pFHA.3524@.tk2msftngp13.phx.gbl...
> This has me stumped.
> I have a parent and a child table with a constraint on the parent's
> foreign key in the child.
> So with this in mind, every once in a while when I am exporting data, I
> get an error that there is a row in the child table with and ID that is
> not in the parent table.
> One of our programmers is using ADO transactions and beginning it in
> source code (not a proc). Then he adds a row to the parent table, gets
> the ID in one proc and adds a row to the child table in another proc.
> Then he commits or aborts when he gets back to code.
> Can you think of any reason why the row in the child table is there, but
> the row in the parent isn't there when exporting data?
> Thanks.
>|||The foreign key constraint is not being enforced.
exec sp_helpconstraint N'tableName'
The second result set contains a list of constraints. Check the
status_enabled column for the foreign key constraint in question. If it's
Disabled, then the constraint will not be enforced.
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:ut9Jr40pFHA.3524@.tk2msftngp13.phx.gbl...
> This has me stumped.
> I have a parent and a child table with a constraint on the parent's
foreign key in
> the child.
> So with this in mind, every once in a while when I am exporting data, I
get an error
> that there is a row in the child table with and ID that is not in the
parent table.
> One of our programmers is using ADO transactions and beginning it in
source code (not
> a proc). Then he adds a row to the parent table, gets the ID in one proc
and adds a
> row to the child table in another proc. Then he commits or aborts when he
gets back
> to code.
> Can you think of any reason why the row in the child table is there, but
the row in
> the parent isn't there when exporting data?
> Thanks.
>