Mysql Query to select duplicates

Question

I have a table called Player in my database, it looks like this

 id    placex    placey    wealth    strenght    goalx    goaly 
 ---------------------------------------------------------------    
  1     500        20       3335      2500      500      440 
  2     500        20       777      2000        20      500 
  3      20       500       1000      2000      500       20 
  4     500       440       1000      2000       20       20

I need a query that would display id and wealth on a condition that players a located in the same location so there should be a duplicates of placex, placey for different players

In this table we would have player with id 1 and player with id 2 in the same place, I wrote this

SELECT 
    id, placex, placey, wealth 
FROM 
    Player 
WHERE 
    placex IN (SELECT placex FROM Player HAVING COUNT(id) > 1);

and I get this as output

+----+--------+--------+--------+
| id | placex | placey | wealth |
+----+--------+--------+--------+
|  1 |    500 |     20 |   3335 |
|  2 |    500 |     20 |    777 |
|  4 |    500 |    440 |   1000 |
+----+--------+--------+--------+

I need to eliminate player with id 4, so how do i modify my query to include also dups of placey

I expect to get this

| id | placex | placey | wealth |
   1   500        20      3335
   2   500        20       777

i expect to get same output as i got with quary i wrote except without last row, so there should be no player with id 4 there — Garmagon117
– Garmagon117, Commented Dec 7, 2013 at 19:58

LHA · Accepted Answer · 2013-12-07 20:07:53Z

1

RUN this and let me know your result:

SELECT A.id, A.placex, A.placey, A.wealth
FROM Player A,
     ( SELECT  placex, placey
       FROM Player
       GROUP BY placex, placey
       HAVING COUNT(*) > 1
     ) B
WHERE A.placex = B.placeX
AND A.placey = B.placey

answered Dec 7, 2013 at 20:07

LHA

9,7158 gold badges56 silver badges92 bronze badges

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

Comments

Mihai · Accepted Answer · 2013-12-07 20:06:06Z

0

Select id, wealth From Player 
inner Join (
Select PlaceX, PlaceY From Player Having(count(id) > 1) Occupied
On Occupied.PlaceX = Player.PlaceX and Occupied.PlaceY = Player.PlaceY

Always look at join before sub query gives you way more options.

edited Dec 7, 2013 at 20:06

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

answered Dec 7, 2013 at 19:59

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

2 Comments

Garmagon117 Over a year ago

My school server wont let me use occupied, gives me an error><

Tony Hopkinson Over a year ago

Um change occupied to B then. Tend to make my aliases sort of meaningful, but as long as they are unique to the query, doesn't matter waht they are, as demonstrated by the answer you accepted.

Mihai · Accepted Answer · 2013-12-07 20:10:58Z

0

SELECT  id,placex,placey,wealth FROM player WHERE placey IN(
SELECT  placey FROM Player
GROUP BY placey 
HAVING COUNT(placey)>1)

SQL Fiddle

edited Dec 7, 2013 at 20:10

answered Dec 7, 2013 at 20:05

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

1 Comment

Mihai Over a year ago

@user2977404 Its the same thing as Loc solution but his is faster.

Pang · Accepted Answer · 2015-10-03 02:43:40Z

0

SELECT p1.id, p1.wealth
FROM Player AS p1
INNER JOIN Player AS p2
ON (p1.id <> p2.id AND p1.PlaceX = p2.PlaceX AND p1.PlaceY = p3.PlaceY)
GROUP BY p1.id

You should also add an index to index both PlaceX and PlaceY together.

edited Oct 3, 2015 at 2:43

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Dec 7, 2013 at 20:06

Kessem Lee

1,37310 silver badges14 bronze badges

Collectives™ on Stack Overflow

Mysql Query to select duplicates

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related