I have a problem with getting left join (with or without) in aggregate to work.
I have two tables magiskole and character_magiskole, where magiskole.id is foreign key in character_magiskole (not madatory).
I want to join the two tables, so I show all magiskole records and add info from character_magiskole, on the records where the character_magiskole have a magiskole.
I'm quite sure the right way to do it is using left join, but no matter which join I use, I get the same result (as shown in the screen shots).
I have also tried with adding nullIdentifier in the joins the where clause, as mentionen in other posts - with same result.
What am I doing wrong?
With nullidentifier
I have this filter applied to filter on the correct character.
Hi Claus,
It is necessarily because of the filter. Consider if what you have on the filter is not part of the match condition you want to have. In this case you would move the filter and add it to the match condition.
Cheers,
João
With or without matches indeed the LEFT JOIN, while the Only with relation matches the INNER JOIN.
The links above explain better than I could, the difference between them with a few examples.
If joining the two tables return you the same results, then it means that the table Magiskole has one exact matchin the Character_magiskole (between the MagiskoleId attribute in the first table and the Id in the latter), and in that case a LEFT JOIN and an INNER JOIN return the same results.
You should use the With or Without if you want all the Magiskole records, even if they don't have a match in Character_magiskole entity, while you should use the Only With relation if you want only the Magiskole records that have a match in the Character_magiskole entity.
Kind Regards,João
Thanks Joäo,
My issue is, that when I use With or Without, I don't get all the Magiskole records, even if they don't have a match in the character_magiskole, so it acts like it is a Only with relation.
I guess it is due the filter, where I filter character_magiskole on character.id...
But if that is the case, how do I make a With or Without, where I get all records from Magiskole and relevant records from character_magiskole?
Kind regards,
Claus
That worked!
Thanks Joâo!