mysql select with inner join giving me looping result

Question

Hi folks I am stumped with a problem I'm getting with the following mysql query:

SELECT 
tp.ID AS tpid, 
p.firstName AS pfname, 
p.lastName AS plname, 
p.dob AS pdob, 
fix.homeTeamID AS fixhtid

FROM fixtures AS fix

INNER JOIN teamSeason AS ts ON fix.homeTeamID = ts.ID
INNER JOIN teamPlayers AS tp ON tp.teamSeasonID = ts.ID
INNER JOIN person AS p ON tp.personID = p.ID

WHERE fix.homeTeamID =2

I am expecting 9 rows to result with this query but instead there are over 180 rows where the original 9 rows are being repeated 19 times.

I'm not sure how to troubleshoot this and was wondering if someone could help me - my brain is mush.

I have tried to remove "fix.homeTeamID AS fixhtid" from the query but it does not stop the looping result.

Is there some way to only show the result without looping?

Should I post the related mysql tables online so you can see?

Thanks

Pete

UPDATE

I have updated my sql query to this:

SELECT 
tp.ID AS tpid, 

p.firstName AS pfname, 
p.lastName AS plname, 
p.dob AS pdob,

fix.homeTeamID AS fixhtid


FROM fixtures AS fix

INNER JOIN teamSeason AS ts 
ON ts.ID=fix.homeTeamID

INNER JOIN teamPlayers AS tp 
ON tp.teamSeasonID = ts.ID

INNER JOIN person AS p 
ON p.ID = tp.personID

WHERE fix.homeTeamID =2

GROUP BY tpid

as suggested by Deepu. I added the "GROUP BY" and it has fixed my result. Thank you so much Deepu and everyone else that took the time to respond.

Second Update

I have updated the query again to this:

SELECT DISTINCT tp.ID AS tpid,
    p.firstName AS pfname,
    p.lastName AS plname,
    p.dob AS pdob,

    fix.homeTeamID AS fixhtid

    FROM fixtures AS fix

    INNER JOIN teamSeason AS ts
    ON fix.homeTeamID=ts.ID

    INNER JOIN teamPlayers AS tp
    ON tp.teamSeasonID=ts.ID

    INNER JOIN person AS p
    ON tp.personID=p.ID

    WHERE fix.homeTeamID=2

as suggested by Strawberry and John in the name of best practice I have replaced "GROUP BY" in place of "SELECT DISTINCT"

Thanks folks. I have learned something new.

Answer 1

You want the JOINS to ADD data based on your original data from fixtures. Then you should use LEFT JOIN