MySQL Query return duplicate rows (No duplicates in table)-CodePudding

I am fairly new to MySQL, I wrote this query

SELECT 
        r.id as rID,
        r.title as rTitle,
        r.researcherName as rName,
        r.volumeID as rVolID,
        r.views as rViews,
        r.downloads as rdowns,
        vol.date as volDate,
        vol.title as volTitle,
        vol.versionID as verID,
        ver.title as verTitle
        FROM journalsResearches AS r 
        INNER JOIN  versions as ver
        INNER JOIN  volumes as vol
        ON r.volumeID = vol.id
        AND r.volumeID = 12
        ORDER BY r.views DESC

and expected result was 1 row and for some reason, this row has been duplicated as a result and still one row in table

CodePudding user response：

You forgot a condition on the join with the 'versions' table, so your query might actually be returning one row per row in 'versions'.

CodePudding user response：

Fixed with rearranging tables in JOIN statement and adding ON condition on versions

SELECT
        r.id as rID,
        r.title as rTitle,
        r.researcherName as rResearcherName,
        r.views as rViews,
        r.downloads as rDownloads,
        vol.id as volID,
        vol.title as volTitle,
        vol.date as volDate,
        ver.id as verID,
        ver.title as verTitle
        FROM
        journalsResearches AS r
        INNER JOIN  volumes as vol 
        INNER JOIN  versions as ver 
        ON r.volumeID = vol.id 
        AND vol.versionID = ver.id 
        AND vol.id = 12
        ORDER BY r.views DESC