CODEWITHSUNDEEP
mysqlsql
cnian
cnian

Reputation: 249

Sql query to select different row with same ID

I have a simple table and I wanted to get a row with same ID but different values.

lets assume I don't know the IDs;

lets say I have;

I have feature "one" and I want to find the feature that has same ID with feature "one".

Upvotes: 1

Views: 3682

Answers (3)

JimNicholson
JimNicholson

Reputation: 351

As Strawberry commented, you could use an inner join:

SELECT f2.feature
FROM feature f1
INNER JOIN feature f2 ON f1.id = f2.id AND f1.feature <> f2.feature
WHERE f1.feature='one'

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

I'm going to go with 'funky'; 

SELECT y.*
  FROM my_table x
  JOIN my_table y
    ON y.id = x.id 
   AND y.feature <> x.feature
 WHERE x.feature = 'one';

Upvotes: 1

Mureinik
Mureinik

Reputation: 311063

One way to do this is with the exists operator:

SELECT *
FROM   features f_outer
WHERE  EXISTS (SELECT *
               FROM   features f_inner
               WHERE  f_outer.id = f_inner.id AND
                      f_outer.feature != f_inner.feature AND
                      f_inner.feature = 'one')

Upvotes: 0

Related Questions