Can't make simple LEFT or INNER Joins working on SharePoint Online

[Joao Livio]

Hello!

I have a problem and asking for help so any comments will be very valuable to me.

At this point I really can't make simple LEFT or INNER Joins working on SharePoint Online, this is a result of a Migration from On Premisses.

I don't know if the problem is in the PnP JS Framework, the Lookup Fields or the Content Types.

So at this point I'm I'm very confuse and really question me if I lost my skills in Caml Queries

This is the Structure

My Main List

 

PS

I do not know if there is a problem the list name is equal to lookup field name

Can someone kindly can help me with this simple Caml? Just to confirm where is the problem.

Thank you

[#2]

Hi, first of all thank you to all that spend time reading my post.

Here are my conclusions.

1. This site collection is a result from a on premise migration;
2. One thing that keep my eyes, was I have exactly the same number of records in all lists.

The problems:

Who migrated don’t keep in mind the items IDs, so all lookups query wrong information relative to other lists.

Even with the same records, there are duplicates and missing items.

The content types are in the wrong order.

Finally of course the Caml Query results are wrong. The scope of this post was just to double check what I was missing.

Thanks to all