CODEWITHSUNDEEP
selectsql-server-2008-r2
Furkan Gözükara
Furkan Gözükara

Reputation: 23860

How to select multiple columns values same rows from single table

I have a SQL Server table. Now this table has columns like primary key Id, A, B, C, D, E, F, G

Now I want to select rows from this table like this

A=A, B=B, C=C, D=D and G > 132

So I am trying to select rows from this table which rows A,B,C,D columns has same data and G column data > 132.

So how can I do that ? Thank you.

I tried this query but returning same Id rows 

    SELECT TableA.Id,TableA.UserId,TableA.MaximumHp,TableA.Attack,TableA.Defense,TableA.SpAttack,TableA.SpDefense,TableA.Speed
FROM myTable as TableA
Inner Join myTable as TableB on 
TableA.MaximumHp = TableB.MaximumHp
  AND TableA.Attack = TableB.Attack
  AND TableA.Defense = TableB.Defense
    AND TableA.SpAttack = TableB.SpAttack
      AND TableA.SpDefense = TableB.SpDefense
        AND TableA.Speed = TableB.Speed
       AND TableA.Id != TableB.Id

SQL Server 2008 R2

Upvotes: 0

Views: 5681

Answers (2)

Isaac Fife
Isaac Fife

Reputation: 1690

I THINK what you mean is duplicates. Tell me if this is what you are looking for.

SELECT [Table].A, [Table].B, [Table].C, [Table].D, [Table].E, [Table].F, [Table].G
FROM [Table] LEFT JOIN (SELECT A, B, C, D FROM [Table] 
GROUP BY A, B, C, D
HAVING count(*) > 1)
AS sub ON ([Table].A=sub.A) AND ([Table].B=sub.B) AND ([Table].C=sub.C) AND ([Table].D=sub.D)
WHERE G>132 and sub.A is not null;

This will give you all the rows where a,b,c, and D are equal to another row in the table...and G > 132

Upvotes: 3

John Hartsock
John Hartsock

Reputation: 86892

Sounds like you want to join the table to itself

SELECT *
FROM Table t1
Inner Join Table t2 on t1.A = t2.A
  AND t1.B = t2.B
  AND t1.C = t2.C
  AND t1.D = t2.D
  AND t1.G > 132
  AND t1.ID <> t2.ID

Upvotes: 4

Related Questions