Fix Join table functions or not duplicate values?

So I have a query in NaviCat, it has a part that looks like this:

case
when base.table = 1 then Table1.Name
when base.table = 2 then Table2.Name
when base.table = 3 then Table3.Name
when base.table = 4 then Table4.Name 
end as Name

As the Base table has the Identifier Number, but does not contain the Names of the person. It is connected properly and works. However, It is tripling some rows in certain cases.

I can get a response back that looks similar to this

Identifier         Amount              Name
12                 1000                Smith, Suzy
12                 1000                Smith, John
12                 1000                Smith, John & Smith, Suzy

I would like it to only return the longest entry (as all the Names are either Husband, Wife, or Husband & Wife), as all the amounts are the same and I think it is because I am left joining the Base table to the Table1, Table2, etc. But how can I fix this? Is there a function to only return the longest Name?

I am looking at a few left joins similar to this.

Left join server.table1 as Table1 on Base.Identifier = Table1.Identifier AND Base.Date = Table1.Date

each table1-table4 has the same join code.

Answer

I can only provide a “raw” SQL solution, as I never used NaviCat.
Below solution assumes that for every Identifier in any of table1–table4, the Amount and the Date values are the same.
Your FROM should be changed like this:

left join (
    select Identifier, Amount, Date, MAX(LEN(Name)) as LongestName
    from server.table1
    group by Identifier, Amount, Date
    UNION ALL
    select Identifier, Amount, Date, MAX(LEN(Name)) as LongestName
    from server.table2
    group by Identifier, Amount, Date
    UNION ALL
    select Identifier, Amount, Date, MAX(LEN(Name)) as LongestName
    from server.table3
    group by Identifier, Amount, Date
    UNION ALL
    select Identifier, Amount, Date, MAX(LEN(Name)) as LongestName
    from server.table4
    group by Identifier, Amount, Date
    ) as tables1to4 on Base.Identifier = tables1to4.Identifier AND Base.Date = tables1to4.Date

and your case expression becomes only

tables1to4.LongestName as Name

and the same applies to Amount, if it is needed in the end result (no case necessary).