I am trying to put query together that will get the information for the Date, Place, Place Details, Description, Citation, and media fields on the following screen:
 |
| Pic 1 |
The put the following query together to get the above information:
select et.ownerid,
nt.surname,
nt.given,
et.eventid,
et.eventtype,
nt.birthyear,
ft.Name,
et.placeid,
et.siteid,
substr(et.date,4,4) as Ev_Date,
–(select pt.name
— from placetable pt
— where et.siteid = pt.placeid) as Place,
pt.name as Place,
site.name as Place_Details,
trim(et.details) as Description,
ml.mediaid,
ml.linkid,
–mm.mediatype,
(select m1.mediapath
from multimediatable m1
where ml.MediaID = m1.mediaid) as Path,
(select m2.mediafile
from multimediatable m2
where ml.MediaID = m2.mediaid) as File,
(select m3.caption
from multimediatable m3
where ml.MediaID = m3.mediaid) as Caption,
(Select s1.name
from sourcetable s1
where ct.sourceid = s1.sourceid) as Source,
(Select s2.actualtext
from sourcetable s2
where ct.sourceid = s2.sourceid) as Actual_Text,
(Select s3.comments
from sourcetable s3
where ct.sourceid = s3.sourceid) as Comments
from eventtable et,
placetable pt,
facttypetable ft,
nametable nt,
sourcetable st
left join medialinktable as ml
on et.ownerid = ml.ownerid
left join citationtable as ct
on et.ownerid = ct.ownerid
left join placetable as site
on et.siteid = site.placeid
where et.ownerid = nt.ownerid
and et.eventtype = ft.facttypeid
and et.placeid = pt.placeid
and ct.sourceid = st.sourceid
and et.EventType = 29
and et.ownerid = 1490
order by nt.surname, nt.given, ev_date
;
I am getting the Event Date, Place, Place Details and Description.
I am not getting the following information:
 |
| Pic 2 |
Nor this information:
 |
| Pic 3 |
I am getting the source but it is not correct. I am not getting anything for Actual_Text or Comments.
I am getting the filepath which appears to be correct. I am getting a filename and caption but they are not correct.
I am getting too many rows
I want a row for every eventtype of 29 from the eventtable. If there is a place, place detail, description, mediapath, mediafile, caption, source, actual_text or comments, that it appear in the query results. If there isn’t data, the field be blank but the row to be displayed.
I want to be able to sort it my surname, given and eventdate.
I am missing something somewhere. Can you point me in the right direction or tell me what I am doing wrong?
Discussions & comments from Wikispaces site
Will produce big results set
25 October 2017 22:10:39
Because for each event for a person, there can be multiple citations AND multiple media. If an event has 2 of each, there will be 4 rows; 3 of each, 9 rows… And if you are also after the Master Source media and/or the Source Details media for which there can be more than one in each case, then it can exponentiate even faster. Are you sure that is what you really want? It’s one reason why RM has all these sub-windows.
momakid
26 October 2017 02:26:13
I have residence events for attaching the census image. They do have the month and year of the census. There should be only one citation and on a few occasions 2 images. If there is more than one citation, then I need research it and delete the extras. But on a whole there should be only one. There should be multiple years on a lot of them. I am trying to determine which records are missing census images, which ones are missing citations and sources. Which ones are missing a census year.
A family member had a shoe box full of funeral cards. I scanned them and started attaching them to the death event. But I soon discovered most of those women names were married names. I didn’t know their maiden names. So I created alternate names consisting of given name and maiden name for the given name of the alternate record. The surname was the married name and the suffix was the husbands given name. I was now able to find a big majority of the women.
BUT i think that could affect my results. I am not going to restrict it to the primary record first but I have a feeling I am going to have to. I did do a query to see how many residence records there were and there were 3,414. I am trying to think of why I should not get at least that many records in my results. I want to get every residence event. I may need to restrict it to A thru G, H thru K, etc. I will dump the results into an excel spreadsheet and work from it.
I did discover that I needed to look at the eventid and ownertype of 2 for the citation and medialinktable .I am getting the path, file, caption and source but not the source details.
This is what I have now:
select et.ownerid,
nt.IsPrimary,
nt.surname,
nt.given,
et.eventid,
et.eventtype,
nt.birthyear,
ft.Name,
et.placeid,
et.siteid,
substr(et.date,4,4) as Ev_Date,
pt.name as Place,
site.name as Place_Details,
trim(et.details) as Description,
ml.mediaid,
ml.linkid,
–mm.mediatype,
(select m1.mediapath
from multimediatable m1
where ml.MediaID = m1.mediaid) as Path,
(select m2.mediafile
from multimediatable m2
where ml.MediaID = m2.mediaid) as File,
(select m3.caption
from multimediatable m3
where ml.MediaID = m3.mediaid) as Caption,
(Select s1.name
from sourcetable s1
where ct.sourceid = s1.sourceid) as Source,
(Select s2.actualtext
from sourcetable s2
where ct.sourceid = s2.sourceid) as Actual_Text,
(Select s3.comments
from sourcetable s3
where ct.sourceid = s3.sourceid) as Comments
from eventtable et,
placetable pt,
facttypetable ft,
nametable nt,
sourcetable st
left join medialinktable as ml
on et.eventid = ml.ownerid and ml.ownertype = 2
left join citationtable as ct
on et.eventid = ct.ownerid and ct.ownertype = 2
left join placetable as site
on et.siteid = site.placeid
where et.ownerid = nt.ownerid
and et.eventtype = ft.facttypeid
and et.placeid = pt.placeid
and ct.sourceid = st.sourceid
–and nt.isprimary = 0
and et.EventType = 29
–and et.ownerid = 1490
order by nt.surname, nt.given, ev_date;