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.
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).