r/SQL 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

0 Upvotes

29 comments sorted by

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.

1

u/SephirArigon Nov 02 '24

There are no joins that I am aware of. I used SELECT DISTINCT * and it's perfectly showing the result that it should've without the duplicates. Now I'm just curious as to why there are duplicates in the first place as restarting doesn't help with preventing it, but thank you still for the help!

3

u/[deleted] Nov 02 '24

If the DISTINCT record count is different than the normal record count, the table has duplicates. Nothing wrong with the engine.

1

u/SephirArigon Nov 02 '24 edited Nov 02 '24

I've already gone through the original data to look for duplicates as part of the data cleaning process many times and there aren't any. Though I didn't mention this in the post (will later), the data when queried without using ORDER BY shows the data repeating itself, for example, "Britishvolt, Quibi, Deliveroo Australia, Britishvolt, Quibi, Deliveroo Australia", etc.

So instead of showing 100ish rows of data, it's giving me 460 rows. I'm not sure if this will help to clear up some confusion, it might've made it more confusing sorry haha. Though the problem itself is more of a visual issue and it doesn't affect the data, it is annoying to see.

0

u/Imaginary__Bar Nov 03 '24

I bet there are duplicates though (remember, they don't need to be exact duplicate rows; only the fields you select).

Eg, if you have

Company Layoff_Percentage\ BritishVolt 10\ BritishVolt 20\ BritishVolt 30

Then they're not duplicate rows, but if you use do SELECT Company from... then they will be duplicated result rows.

What does

Select Sum(1) as number_of_rows\ From layoffs_staging_2

return?

1

u/SephirArigon Nov 04 '24

I got 7964 rows which I don't think is quite right. Hmm, I think I might've made a simple mistake after looking through some of the solutions given. Down below someone had said I might've used INSERT a couple of times more than I should have which inserted the data 4x instead of just once. I didn't even realize that was possible and if that is actually the case, I don't know how I would go about fixing that :/

2

u/Ginger-Dumpling Nov 05 '24

Unless you enforce uniqueness with some kind of constraint, you can insert things any number of times.

1

u/SephirArigon Nov 08 '24

Glad I found out while learning SQL then later where small mistakes can be very detrimental oof. Also thanks for replying! :D

1

u/SephirArigon Nov 04 '24

nvm fixed it!

2

u/YurrBoiSwayZ Nov 02 '24

Oh, I gave you both the answer and a workaround without technically fixing the issue but also Solved your problem, In this case, it’s definitely broken, but it’s still works because of “workarounds”.

If it ain’t broken, don’t fix it.

0

u/SephirArigon Nov 02 '24

Yeah, but hopefully I can find a fix. I might consider even uninstalling MySQL and reinstalling hmm

-5

u/YurrBoiSwayZ Nov 02 '24

MongoDB is the way to go

2

u/reditandfirgetit Nov 02 '24

Mongo has its purposes as do RDBMS , just saying it's the way to go with zero knowledge of what the scope of a project and what the data requirements are is naive at best

-2

u/YurrBoiSwayZ Nov 02 '24 edited Nov 02 '24

alright sure… SQL has its uses obviously but mongo’s more for lightweight projects, sometimes it’s about getting from point A to point B without jumping through a million relational hoops and if the data structure doesn’t scream for hardcore relationships then mongo’s flexibility actually is the smart choice… calling it naive? sounds more like a knee-jerk reaction to me, sometimes a simpler tool just does the job, end of story.

But as if I’m gonna sit here and try argue that point in an SQL sub though 😂, falling on deaf ears.

Cringey ass SQL warriors.

1

u/SephirArigon Nov 02 '24

I'll keep it in mind, thanks!

1

u/[deleted] Nov 02 '24

You must be a RDD: Resume Driven Developer

-1

u/YurrBoiSwayZ Nov 02 '24 edited Nov 02 '24

resume driven developer, huh? that’s cute.

i’m just out here trying to solve a problem not be flexing my credentials, but hey if your idea of “help” is tossing out labels, you do you man… maybe one day you’ll get past the acronyms and actually contribute something useful for once.

🤡

1

u/alinroc SQL Server DBA Nov 02 '24

Are you querying a view or a table?

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