Forum Discussion

krishna_murthy's avatar
krishna_murthy
Helper II
1 year ago
Solved

Inner Join count rows

Hi Team,   Please find the below table and do the Table1: ID, Name 1, A 1, B  table 2: ID, Location 1,D 1,E 2,F   What is the Inner join count rows? what is the left outer join count ro...
Need a help
  • ashleyfiore's avatar
    ashleyfiore
    1 year ago
    Inner Join Row Count :=
COUNTROWS (
    GENERATE(
        Table1,
        FILTER(
            Table2,
            Table2[ID] = Table1[ID]
        )
    )
)

     

    Left Outer Join Row Count :=
COUNTROWS (
    GENERATE (
        Table1,
        VAR MatchingRows =
            FILTER (
                Table2,
                Table2[ID] = Table1[ID]
            )
        RETURN
            IF (
                COUNTROWS(MatchingRows) > 0,
                MatchingRows,
                ROW ( "ID", BLANK(), "Location", BLANK() )  -- simulate left join with blanks
            )
    )
)
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

krishna_murthy 

 

An inner join will return only the rows where there is a match in both tables based on the ID column.

ID, Name, Location
1, A, D
1, A, E
1, B, D
1, B, E

 

A left outer join will return all rows from Table1 and the matched rows from Table2. If there is no match, the result is NULL on the side of Table2.

ID, Name, Location
1, A, D
1, A, E
1, B, D
1, B, E