Mysql select - certain rows with duplicate column

  • Nem
  • Guru
  • Guru
  • Nem
  • Posts: 1243
  • Loc: UK

Post 3+ Months Ago


I need some help with a query not sure if it's simple or not lol.

I have a table with is an association with another table.

The layout of this table is like so:

| assoc_id | person_id |

The assoc_id is not unique, it has multiples of the same Id.

What i want to do is select certain person_id's as long as the assoc_id is the same.

So for example:

I tried -
Code: [ Select ]
SELECT * FROM assoc WHERE person_id=3 AND person_id=5 AND person_id=10

However, even though those Ids exist, mysql returns 0 records found.

Also how can i do a query to find the records with the same assoc_id ?

All help is appreciated, please enlighten me! lol.
  • Danny1337
  • Student
  • Student
  • Danny1337
  • Posts: 70
  • Loc: Norway

Post 3+ Months Ago

Not sure what you mean but i know that person_id can't be 3,5 and 10 at the same time

Maybe you mean OR instead of AND in the sql query?
If you want to select a row where assoc_id and person_id is the same, you could do
Code: [ Select ]
SELECT * FROM assoc WHERE person_id = assoc_id ASC LIMIT 1
  • joebert
  • Genius
  • Genius
  • User avatar
  • Posts: 13511
  • Loc: Florida

Post 3+ Months Ago

I'm probably as confused about what you want returned as you are about why it's not working. :)

This would be easier if you posted a small example table and the results you expect to have returned.
  • IcyDragoon
  • Student
  • Student
  • IcyDragoon
  • Posts: 65

Post 3+ Months Ago

Step 1: Move the non duplicates (unique tuples) into a temporary table
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!
DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table
RENAME TABLE new_table TO old_table;

thats the one dupe removal for me, not great for huge databases*.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: PolishHurricane and 27 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum

© 1998-2017. Ozzu® is a registered trademark of Unmelted, LLC.