April 20, 2002 at 11:56 am
I have what appears to be a very simple task that I simply cannot see a way to do. I must be approaching it wrong, any help is appreciated.
The following illustrates what I am trying to do. Based on the sample below, my goal is to determine the matching Family Surname(s) by providing the given names of as many members of the family as I know.
Table1
RowID SurnameID Surname
1 1 Flintstone
2 2 Rubble
Table2
RowID SurnameID GivenNameID
1 1 3
2 1 2
3 2 1
4 2 4
Table3
RowID GivenNameID GivenName
1 1 Barney
2 2 Fred
3 3 Wilma
4 4 Betty
The goal is to answer the following question:
when given one or more FirstName values, find the Surnames containing those FirstName values.
For instance, when given 'Fred', return 'Flintstone'. When given 'Barney' AND 'Betty', return 'Rubble'. When give 'Fred' and 'Barney', return nothing.
The problem is when I want to use more than one given name.
SELECT Surname
FROM TABLE1 AS T1 INNER JOIN Table2 as T2 ON
T1.SurnameID = T2.SurnameID INNER JOIN TABLE3 AS T3 ON T2.GivenNameID = T3.GivenNameID
WHERE T3.GivenName = 'Barney' AND T3.GivenName = 'Betty'
But T3.GivenName can't equal both values simultaneously and using OR produces a larger and less accurate result set than I want.
I have a fundamental flaw in the way I am approaching this, but I just can't see it.
Thanks for your time.
JK
April 20, 2002 at 2:48 pm
Well, I came up with the following solution. I used subqueries to return the SurnameIDs of all Surnames that included the requested Given Name, used AND IN to determine if there were any surnames common to all the result sets
SELECT DISTINCT T0.SURNAME
FROM Table1 AS T0
WHERE
T0.Surname IN (SELECT T1.Surname
FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.SurnameID = T2.SurnameID INNER JOIN Table3 AS T3 ON T2.GivenNameID = T3.GivenNameID
WHERE T3.GivenName = 'Barney')
AND
T0.Surname IN (SELECT T4.Surname
FROM Table1 AS T4 INNER JOIN Table2 AS T5 ON T4.SurnameID = T5.SurnameID
INNER JOIN Table3 AS T6 ON T5.GivenNameID = T6.GivenNameID
WHERE T6.GivenName = 'Betty')
Any comments are welcome.
Thanks,
JK
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply