CODEWITHSUNDEEP
mysql
ngplayground
ngplayground

Reputation: 21617

MYSQL Multiple Joins duplicates the output

SELECT p.id, p.first
FROM people p
LEFT JOIN job j ON ( p.job_id = j.id )
LEFT JOIN favourites f ON ( p.company_id = f.company_id )
WHERE p.company_id = 1

I have 3 tables.

Job Favourites People

Each have company_id inside them. When I try the above it outputs p.id,p.first twice. If I remove one of the JOINs then the output is as expected but without the table which was removed.

Upvotes: 0

Views: 30

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

As I said in the comments what you have here is just an one-to-many relationship which gives you exactly what you are saying.

Like you have a registry on people and you have two favourites to this people on table favourite. To clarify consider the following situation:

Table people:

id   name        job_id    first
 1     John         1        1
 2     Campos       2        2

Table job 

id   job
1     Programmer
2     Developer

Table favourites

company_id   desc
 1            Blah
 2            Bleh
 1            Blih

On the above model a people have two favourites registries.

For your model it seems that one people can have only one job, but since you did not specify what the table favourites looks like it is probably the the situation i've shown is generating your problem. So to quickly solve it you can use a DISTINCT command like:

SELECT DISTINCT p.id, p.first
  FROM people p
       LEFT JOIN job j 
              ON ( p.job_id = j.id )
       LEFT JOIN favourites f 
              ON ( p.company_id = f.company_id )
 WHERE p.company_id = 1

Upvotes: 2

Related Questions