I have 3 tables which I need to join.

Table animal is the main table. It contains:


Table fathers is table which contains data about father. It is related to table animal by FATHER_ID. It looks like that:


Table breed is table which contains data about breed. It is related to table animal by BREED_ID. It looks like that:


What I need is to display table which would look like that:
LONG ID(animal table) | NAME(animal table) | BREED NAME(breed table where BREED_ID = BREED_ID(from animal table)) | FATHER ID (animal table) | FATHER NAME (father table where FATHER_ID = FATHER_ID(from animal table)

For that I am using this sql query:
SELECT a.LONG_ID, a.NAME, b.BREED_NAME, a.FATHER_ID, f.FATHER_NAME
FROM animal a
LEFT JOIN breed b ON b.BREED_ID = a.BREED_ID
LEFT JOIN fathers f ON f.FATHER_ID = a.FATHER_ID;


And my table shows 3,064 entries but there are only 1,773 entries in animal table. Many double entries appears in data table. Why? Is my sql query wrong?

All I needed was to add

Hello,

It seems duplicate records are located in "fathers" or/and in "breed" table(s). We can check it if you send an SQL dump of your tables or provide a temporary access to your J! backend by email so we can investigate the problem in more details.

As a temporary solution you can try the following query:



Regards,
ARI Soft