Quote from kevync on 2024-02-17, 10:49 pm

ps. if I had to do the Event/Media/Citation in a  table by itself -- I could work with that also

 

Quote from thejerrybryan on 2024-02-18, 12:11 am

Without confirming by testing it myself, I think you need the following.

left join CitationLinkTable as clt on clt.OwnerType = 2 and clt.OwnerID = et.EventID

After that, I don't know if you want the media for the event or the media for the citation. So let's suppose you want media for the citation. Then I think you need the following.

join CitationTable as ct on ct.CitationID = clt.CitationID
left join MediaLinkTable as ml on ml.ownertype = 4 and ml.ownerid = ct.citationid

I would want to think a little bit more about the join vs. left join because you want to keep the null cases and you want to keep the null cases both for events without a citation and for citations without media. Or at least that's the way I understand the question. Because two left joins are necessary, you might need to hide one or both of them inside a subquery.

When I stack multiple left joins in a row I don't often get the results I anticipate, and I don't have time tonight to test out these finer details. In any case, ml.OwnerType = 4 is to pick up media links to citations and clt.OwnerType = 2 is to pick up citation links to events.

This particular query would have been slightly easier in RM7 because the CitationTable included the links and there was no CitationLinkTable. With RM9, I have become accustomed to making a subquery of the CitationLinkTable and the CitationTable where the subquery then acts just like the CitationTable did in queries for RM7.

If I were doing this for myself, I would add the join to CitationLinkTable and make sure that part is correct before moving on to add the join to MediaLinkatabel.

Quote from kevync on 2024-02-18, 9:03 am

Yes those joins / venn diagram trip me up sometimes. For my purposes I want media for the event (either  ID or null) as I only have one set of facts that have citations for (nearly) all  --burial / find-a-grave.  Hopefully that makes sense

below for example gives me media but only if media is present (but not for nulls)

-- all Facts / place by person
select pt.PersonID, nt.Surname, nt.Given, substr(et.Date, 4,4), et.EventID, ftt.Name, plt.PlaceID, ml.MediaID
from nameTable as nt
join PersonTable as pt on nt.OwnerID = pt.PersonID
join EventTable as et on et.OwnerID = pt.PersonID AND et.OwnerTYPE = 0
join FactTypeTable as ftt on et.EventType = ftt.FactTypeID
join PlaceTable as plt on et.PlaceID = plt.PlaceID
join MediaLinkTable AS ml ON ml.OwnerType = 2 AND ml.OwnerID = et.EventID

 

Quote from thejerrybryan on 2024-02-18, 10:26 am

left join MediaLinkTable AS ml ON ml.OwnerType = 2 AND ml.OwnerID = et.EventID

It's the left join rather than the join that will pick up the nulls.

Quote from thejerrybryan on 2024-02-18, 10:43 am

Think of it this way. Suppose you have the following simple query.

select et.EventID, ml.LinkID
from EventTable as et
join MediaLinkTable AS ml ON ml.OwnerType = 2 AND ml.OwnerID = et.EventID

This will list all your events that do have at least one media file and it will not list any events that do not have any media files. There will be no nulls listed anywhere. That's the way a join works.

If you change the join to a left join then it will list all your events. For those events that have media, it will list the ml.LinkID fields. For those events, that do not have any media, the ml.LinkID field will be null.

Of course, this is just a simple example of the concept. For a more practical query, you would surely want to have join's to other tables to be able to display more information.

And remember that I *think* but I'm not sure without testing that because you need two left joins to solve the original problem you might therefore need to hide each of the two left joins inside their own separate subquery and then join the two subqueries together (or maybe do a union of the two subqueries). I'm just a simple country boy, so I might even cheat and make two queries - one to find citations without media and the other to find events without media.

Quote from thejerrybryan on 2024-02-18, 10:45 am

Sorry, I keep thinking of little tidbits to add. If you are really interested mostly in the null cases and you are not interested in the not null cases, then it's straightforward to add a where clause to test for null and to omit the not null cases.

Quote from thejerrybryan on 2024-02-18, 11:42 am

Here's another tidbit.

I learned left join decades ago so I tend to use it a lot. But there is a different and sometimes better way to look for nulls that doesn't use left join at all. Namely, it's using IN or NOT IN.

Here is an example that will list EventID's for events without media files. As usual, you could add additional joins to additional tables to provide additional information in the query. But this little bit of code gets at the core issue of finding the events without media.

select et.EventID
from EventTable as et
where et.EventID not in
(
select ml.ownerid
from MediaLinkTable AS ml
where ml.OwnerType = 2
)

 

Quote from Tom Holden on 2024-02-18, 11:50 am

Quote from kevync on 2024-02-17, 10:33 pm

I need to have the PID, EventID, PlaceID, MediaID, CitationID , also I am aware  I may have multiple rows based on the data by person etc.

I do not want to filter out null values for placeid, mediaid or citationID.

There are potentially media and citations for the Person (General) and for Names in addition to those for Events. Also, there are EventTypes for Family (couples) and Associations that need to either be excluded or have additional processing to be included in order for them to be related to the right person. I think you need to be a bit more explicit about what results you are after.

Currently, your starting point seems to be just to get the Events, EventPlaces, EventMedia, EventCitations (and maybe the latter's CitationMedia). As Jerry has pointed out, the (INNER) JOIN you tried will give results only if every JOIN criterion is satisfied so won't give you any {null}. That's why you need to use LEFT JOIN.

And you will get false results with

SELECT ...
from PersonTable pt
join EventTable as et on et.OwnerID = pt.PersonID

because there may be some events of the Family or Associations type whose OwnerID belongs to a table other than the PersonTable.

Quote from thejerrybryan on 2024-02-18, 12:43 pm

The query you are after can quickly become very complicated. I find when talking about such things it's usually best only to confuse one issue at a time. Therefore, I was focusing on the problem of identifying facts and citations without media files.

But as Tom mentioned, at some point you will need to differentiate between individual fact types and family fact types. Sometimes I think the bane of my existence in dealing with EventTable is dealing with this distinction in a reasonable fashion.

Here is one way to approach the problem. We do a union between individual fact types and family fact types.

For individual fact types, we have rin1 which is the PersonID and rin2 which is null. For family fact types, we have rin1 which is FatherID and rin2 which is MotherID. For a union, the two queries being combined have to have the same number of columns. That's why we have to have rin2 as a null column in the query for individual fact types.

For individual fact types we don't actually have to join to PersonTable to get the PersonID because for individual fact types the OwnerID in the EventTable already is the PersonID. But for family fact types, we do have to join to FamilyTable to get FatherID and MotherID.

select et.EventID, et.Ownerid as rin1, null as rin2
from EventTable as et
where et.OwnerType = 0 and et.EventID not in
(
select ml.Ownerid
from MediaLinkTable AS ml
where ml.OwnerType = 2
)

union

select et.EventID, ft.FatherID as rin1, ft.MotherID as rin2
from EventTable as et
join FamilyTable as ft on ft.FamilyID = et.Ownerid
where et.OwnerType = 1 AND et.EventID not in
(
select ml.ownerid
from MediaLinkTable AS ml
where ml.OwnerType = 2
)