Hi all,
first off I must start with an apology. I know I'm sort of doing the wrong
thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.
It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
<prefix>addresses
<prefix>connections
<prefix>events
<prefix>family
<prefix>person
<prefix>repositories
<prefix>sources
<prefix>texts
The <prefix> changes and is unique for each tree.
There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?
I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.
Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.
The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.
(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
FROM humo1_person
LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
AND (pers_firstname LIKE '%a%'
OR (event_kind='name' AND event_event LIKE '%a%')
)
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo2_person.*, event_kind, event_event, address_place,
address_zip
FROM humo2_person LEFT JOIN humo2_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo2_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
AND (pers_firstname LIKE '%a%'
OR (event_kind='name' AND event_event LIKE '%a%') )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo3_person.*, event_kind, event_event, address_place,
address_zip
FROM humo3_person LEFT JOIN humo3_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo3_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
AND (pers_firstname LIKE '%a%'
OR (event_kind='name' AND event_event LIKE '%a%')
)
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo4_person.*, event_kind, event_event, address_place,
address_zip
FROM humo4_person
LEFT JOIN humo4_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo4_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
AND (pers_firstname LIKE '%a%'
OR (event_kind='name' AND event_event LIKE '%a%')
)
GROUP BY pers_gedcomnumber
)
ORDER BY pers_lastname ASC , pers_firstname ASC LIMIT 0,30
Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
completely off the grid? I do use sqlite quite a bit, but not on this
particular project.
--
Regards,
Michael.j.Falconer.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
