MySQL Query return duplicate rows (No duplicates in table)

Question

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

You're getting a cross product with every row in versions, the results have different verTitle. — Barmar
– Barmar, Commented Dec 11, 2021 at 0:29
There is no different title, the researches table has just one row. I'm also tried to add ON condition for the versions table, but the query return 3 row — Mohamed EL-Gendy
– Mohamed EL-Gendy, Commented Dec 11, 2021 at 17:02

Noam-NS · Accepted Answer · 2021-12-11 00:32:19Z

1

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

answered Dec 11, 2021 at 0:32

Noam-NS

2762 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohamed EL-Gendy Over a year ago

Thanks, I fixed the query by rearranging tables in JOIN statement and adding ON condition on versions

Mohamed EL-Gendy · Accepted Answer · 2021-12-11 17:38:26Z

1

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

answered Dec 11, 2021 at 17:38

Mohamed EL-Gendy

5744 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySQL Query return duplicate rows (No duplicates in table)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related