Hi,
I created this composite entity
Table1Entity has a View called Table1LogView inner joined with Table1. (Relation Table1LogView.JournalId= Table1.JournalId)
EntityKey is JournalId coming from the view
Table1LogView is like this:
SELECT MAX(RECID) AS TABLE1LOGVIEWRECID, JOURNALID, STATUS, DATAAREAID, PARTITION, 1010 AS RECID FROM dbo.TABLE1LOG AS T1 WHERE (STATUS = 0) GROUP BY JOURNALID, STATUS, DATAAREAID, PARTITION
Id JournalId JournalParmId JournalType 1 J1 1A 10 1 J2 2B 15 1 J3 2B 0 1 J4 4C 15 1 J5 4C 15 1 J6 4C 15
Id JournalId Staus 1 J1 0 1 J3 0 1 J4 0 1 J5 0 1 J6 0
LinkedTable1Entity has View called LinkedTable1View inner joined with Table1 (Relation LinkedTable1View.ViewRecId= Table1.RecId)
EntityKey is JournalId coming from Table1
The view is like this
SELECT MIN(RECID) AS VIEWRECID, JOURNALPARMID, ID, DATAAREAID, PARTITION, 1010 AS RECID FROM dbo.TABLE1 AS T1 WHERE (POSTED = 1) GROUP BY JOURNALPARMID, ID, DATAAREAID, PARTITION HAVING (COUNT(RECID) = 2)
select ID,JOURNALID,JOURNALPARMID,JOURNALTYPE From TABLE1ENTITY
I get this
Id JournalId JournalParmId JournalType
1 J1 1A 10
1 J3 2B 0
1 J4 4C 15
1 J5 4C 15
1 J6 4C 15
select ID,JOURNALID,JOURNALPARMID,JOURNALTYPE From LinkedTABLE1ENTITY
I get this
Id JournalId JournalParmId JournalType
1 J2 2B 15
André Arnaud de Cal...
292,111
Super User 2025 Season 1
Martin Dráb
230,934
Most Valuable Professional
nmaenpaa
101,156