CODEWITHSUNDEEP
mysqljoin
Joshua
Joshua

Reputation: 4320

Mysql query with multiple joins, first table repeats incorrectly

Say I have 3 tables:

person:

person_company:

company_address:

I have a mysql query that is as such:

SELECT * FROM person 
JOIN (
    person_company, 
    company_address
) 
ON ( 
    person.person_id = person_company.person_id 
    && person_company.company_id = company_address.company_id 
)

However, when I use the query I have the first entry in the first table repeat about 20 times, and then after that a few "clumps" as well. The issue is that this is creating an incorrect row, like John Smith doesn't work for company A, B, C and D.

I think the issue may be something to do with an assumption I am making that I'm not aware of.

Upvotes: 0

Views: 66

Answers (2)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

Your JOIN syntax is a bit odd, you may have better results by joining tables one at a time. I also added in aliases to the tables, though that is not 100% needed it can help if you join the same table multiple times.

SELECT 

    -- Probably best too select specific fields here as, for example,
    -- person_id exists in both person and person_company
    p.person_id,
    p.first_name,
    p.last_name,
    c.company_id,
    c.company_address_id,
    c.address_id     

FROM person AS p

-- First up join person_company to person as your query indicates an n:1 there
INNER JOIN person_company AS pc
    ON pc.person_id = p.person_id

-- Next join company_address to person_company, again as your query indicates n:1
INNER JOIN company_address ca
    ON ca.company_id = pc.company_id

If that still doesn't work, then the problem is in your data instead of the query!

Upvotes: 1

nurdglaw
nurdglaw

Reputation: 2117

I don't recognise your JOIN syntax. How does this work?

SELECT *
FROM person
JOIN person_company ON person.person_id = person_company.person_id
JOIN company_address ON person_company.company_id = company_address.company_id

Upvotes: 1

Related Questions