CODEWITHSUNDEEP
mysql
Garmagon117
Garmagon117

Reputation: 225

Mysql Query to select duplicates

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

Upvotes: 0

Views: 69

Answers (4)

Kessem Lee
Kessem Lee

Reputation: 1373

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.

Upvotes: 0

Mihai
Mihai

Reputation: 26784

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

SQL Fiddle

Upvotes: 0

LHA
LHA

Reputation: 9645

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

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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.

Upvotes: 0

Related Questions