0
Initial information:
- I am using Mysql;
- Tabela1 has 1,000 lines and is my main table;
- table 2 has 400 lines and contains the additional information I need to complete in table 1
- The key field between the two tables is Cod_cli
- in table 2 there are 50 Cod_cli that are not in Tabela1 and I hope not to add them in the search.
What I need: I would like to search for information in table 2 as long as Cod_cli is the same and exists in table 1. Cod_cli in table2 that are not in Tabela1, should not be added in the search, that is, I need that the search result is always equal to 1,000 lines.
Problem: When executing the query below, using left Join, the result of the search is always 1,050 lines, that is, this search is adding the Cod_cli of the table2 that are not in Tabela1.
Follow the query I’m using that is adding the 50 lines commented above and I was hoping not to add them.
SELECT T1.Cod_cli, T1.nome, T2.OCUPAÇÃO FROM T1 LEFT JOIN T2 on T1.Cod_cli=T2.Cod_cli
Question: I am quite new to using Mysql. What do I need to adapt in the above query in order to solve this search? This seems simple, but I already researched enough and I could not find something that I could solve in practice, because in my attempts changing the above query, always appear syntax errors.
Did you check the tables to see if the extra values are really codes that are not in T1? Could be codes that are repeating themselves, if T2 keys are not unique.
– Jorge Mendes
You’re right. The 50 additional items are exactly the amount of keys that are repeating on T2. Thank you very much for the warning and the solution to the problem. You have some suggestion to join, for example, the "Description" field when the key repeats in a table?
– Peter Godoi
I just solved this theme of concatenating a given field when there are duplicate keys in a table. I used: SELECT Cod_cli, GROUP_CONCAT(Descr_cli separator',') as Description FROM T2 group by Cod_cli
– Peter Godoi