/* NameFind.sql
Lists persons with matching surnames and given names
or persons with soundalikes using SOUNDEX().
Produces a set of results similar to RootsMagic 4 but in spreadsheet form and faster.
2011-11-20 by ve3meo
*/
--EXPLAIN QUERY PLAN
SELECT Found.Surname AS FoundSurname, Found.Given AS FoundGiven, OwnerID AS RIN, NameTable.Surname AS MainName, NameTable.Given AS MainGiven, BirthYear AS Born, DeathYear AS Died FROM
(
SELECT OwnerID, CASE WHEN IsPrimary THEN Surname COLLATE NOCASE ELSE Surname COLLATE NOCASE || ' (alt)' END AS Surname, Given COLLATE NOCASE FROM NameTable 
WHERE 
CASE 
WHEN $UseSoundex(Y/N) LIKE 'Y' THEN SOUNDEX(Surname) = SOUNDEX(@Surname) 
ELSE Surname LIKE @Surname AND Given LIKE '%' || @GivenName || '%'
END
AND
CASE 
WHEN $UseSoundex(Y/N) LIKE 'Y' AND Given NOT LIKE '' AND @GivenName NOT LIKE ''
THEN SUBSTR(SOUNDEX(Given),1,2) = SUBSTR(SOUNDEX(@GivenName),1,2)
ELSE 1
END
UNION ALL
SELECT NameTable.OwnerID, MarrName.Surname COLLATE NOCASE ||' (m)', NameTable.Given COLLATE NOCASE FROM NameTable 
INNER JOIN PersonTable ON NameTable.OwnerID=PersonTable.PersonID
INNER JOIN FamilyTable ON PersonTable.PersonID=FamilyTable.MotherID
INNER JOIN NameTable  MarrName ON FamilyTable.FatherID=MarrName.OwnerID
WHERE PersonTable.Sex=1 AND +NameTable.IsPrimary 
AND 
CASE 
WHEN $UseSoundex(Y/N) LIKE 'Y' THEN SOUNDEX(MarrName.Surname) = SOUNDEX(@Surname) 
ELSE MarrName.Surname LIKE @Surname AND NameTable.Given LIKE '%' || @GivenName || '%'
END
AND
CASE 
WHEN $UseSoundex(Y/N) LIKE 'Y' AND NameTable.Given NOT LIKE '' AND @GivenName NOT LIKE ''
THEN SUBSTR(SOUNDEX(NameTable.Given),1,2) = SUBSTR(SOUNDEX(@GivenName),1,2)
ELSE 1
END
)
AS Found
INNER JOIN NameTable USING (OwnerID) WHERE +IsPrimary
--ORDER BY OwnerID
/* 
ORDER BY commented out so that SQLite Expert Personal can sort on any column 
END OF QUERY 
*/