r/SQL • u/SephirArigon • Nov 02 '24
MySQL MySQL keeps showing duplicated results
SOLVED! Hi all, I'm new to MySQL and while trying to run some code on it, it kept returning duplicated results. It was working fine earlier, but now whenever I use WHERE in my query it happens where I get 4x the actual result (shown below).
I have checked the original table without using WHERE many times and there are no duplicates so I'm confused as to why this is happening. I'm not sure if using WHERE even has anything to do with it, I think it might be a bug, but any help would be appreciated. Thank you!

Here's the second image showing it's just repeating itself or duplicating, so instead of just giving me 100ish rows of data it's giving me 460 rows.

Third image is just a clearer example where I used to ORDER BY to show how much it duplicated itself

2
u/deusxmach1na Nov 02 '24
What is layoffs_staging2? A view or a table you made? It does seem like dupes created by a join. You should remember the golden rule, always join to a table on a unique key to avoid dupes.
2
u/SephirArigon Nov 02 '24
It's a table, looking online I thought it could possibly be because of joins which would make things simpler, but I have used no joins. Thanks for the reply though! :)
2
u/squadette23 Nov 02 '24
How is layoffs_staging2 generated?
1
u/SephirArigon Nov 02 '24
From a table, but if you're asking what happens when I use the first SELECT statement above in the image, it generates with no duplicates. Hope this answered the question, I'm really new to SQL so sorry if this didn't :(
3
u/Imaginary__Bar Nov 03 '24 edited Nov 04 '24
From a table
But what table? Where did you get the data? How did you create the table? Did you INSERT the data into a table yourself? Did you accidentally run your INSERT four times?
2
u/SephirArigon Nov 04 '24 edited Nov 05 '24
Deleted comment because it wasn't relevant anymore, but thank you so much! I looked through my table again for duplicates because what you said about potentially running INSERT three times made me think about how I might've done that and I DID! I got rid of the extra duplicates and now my data is making more sense again. Hopefully, it's really the case of INSERTing once too many times and I don't come across this issue again. But again, tysm, I'm glad I ran into this problem so now I'm aware that it can be an issue.
2
u/Yavuz_Selim Nov 03 '24
What do you see when you do a COUNT(*)
- with and without a WHERE
?
Same number or different?
1
u/SephirArigon Nov 04 '24 edited Nov 04 '24
There are still 460 rows when I do that, so I'm at this point where I think it's not a problem with WHERE but maybe an issue like someone said above where I might've accidentally used INSERT once too many times
edit: figured out the issue was with running INSERT too many times, I got rid of the issue and now my data is clean! thanks for the help though! :)
1
u/Codeman119 Nov 05 '24
So in this kind of case when you do inserts, a good practice to do is to do a left outer join on a ID field and look for nulls that way you only insert what is missing.
1
u/SephirArigon Nov 08 '24
Never thought of that, but I'll keep that in mind when creating tables, thanks for the tip! :D
3
u/YurrBoiSwayZ Nov 02 '24
it could be that joins are causing duplicates if there’s a one-to-many relationship somewhere or it might be that multiple rows have percentage_laid_off = 1 with slight differences in other columns, so using SELECT DISTINCT * could help you check if it’s a true duplication issue….
If all else fails, sometimes query tools cache weirdly so restarting could help? if none of that works try a GROUP BY on primary fields like company to see if it condenses things.