When sampling from foxpro tables using left join, I get duplicate records. The program is written in delphi, I use TADOQuery to connect.

 Baseline: ===================== We have the following tables: People, Countries, Cities.  ===================== Fields in the tables: People: Last Name (varchar 100), First Name (varchar 100), Middle Name (varchar 100), Country Code (int 11) , City Code (int 11) Country: Country Code (int 11), Country (varchar 100) Cities: City Code (int 11), City (varchar 100) ================== === Data: People: Ivanov, Andrey, Vladimirovich, 1, 2 Petrov, Igor, Yuryevich, 2, 1 Smirnov, Ilya, Batkovich, 2, 2 Countries: 1, Russia 2, Non-Russia Cities: 1, City 2, pgt ====================== When selecting, select People. *, Country.Country, City.City from People left join Countries on People.CodeCountries = Countries.CodeCountries left join Cities on People.CodeCities = Cities.CodeCities We get the result: Ivanov, Andrey, Vla  imirovich, Russia, pgt Ivanov, Andrei, Vladimirovich, NeRussia, pgt Petrov, Igor, Yuryevich, NeRussia, Petrov, Igor, Yuryevich, Russia, pgt Smirnov, Ilya, Batkovich, NeRussia, pgt Smirnov, Ilya, Batkovich, Russia, pgt =======================

And the more tables we connect, the more duplicate we get at the output. Such a problem appears only after "crossing" more than two tables.

How to avoid repetitions?

  • one
    And you make a conclusion of all the fields and see why the extra entries are added. You can have some countries have the same id, or people have been added several times. Or did you cite a data set where you have exactly reproduced this error and do not have any other data in the tables? - BOPOH

1 answer 1

Thank you very much! All the fault of the "Collating Sequence" component TADOQuery. After changing the value from "RUSSIAN" to "MACHINE", the sample began to run correctly. This is due to the fact that when "Collating Sequence = RUSSIAN" is not case sensitive => As noted by @RAVON, some records whose IDs contain letters (for example, 1b and 1B) are considered the same => So I got duplicates.