Home Page list
Where is this data?
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:
The put the following query together to get the above information:
substr(et.date,4,4) as Ev_Date,
-- from placetable pt
-- where et.siteid = pt.placeid) as Place,
pt.name as Place,
site.name as Place_Details,
trim(et.details) as Description,
from multimediatable m1
where ml.MediaID = m1.mediaid) as Path,
from multimediatable m2
where ml.MediaID = m2.mediaid) as File,
from multimediatable m3
where ml.MediaID = m3.mediaid) as Caption,
from sourcetable s1
where ct.sourceid = s1.sourceid) as Source,
from sourcetable s2
where ct.sourceid = s2.sourceid) as Actual_Text,
from sourcetable s3
where ct.sourceid = s3.sourceid) as Comments
from eventtable et,
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:
Nor this information:
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?