CODEWITHSUNDEEP
mysqlsql
MirrorMirror
MirrorMirror

Reputation: 188

sql: select rows which have the same column value in another column with a single query

example table:

id | value
----------
1  | abc
1  | cb3
1  | dsf
2  | sss
2  | d3

So if the input is "cb3" I want to get all rows with id 1, if the input is "sss" I want to get all rows with id 2. Can this be done with one query instead of two ? (first query is find id and second query bring rows for found id). Also, would a one query approach be faster ?

Thank you in advance.

Upvotes: 0

Views: 2132

Answers (3)

Yaman Jain
Yaman Jain

Reputation: 1251

You can solve it using inner join also.

SELECT S.* 
FROM dataset AS S 
INNER JOIN dataset AS T 
ON S.id = T.id 
WHERE T.value = 'cb3';

Upvotes: 1

HH1
HH1

Reputation: 598

you could try this : 

SELECT * 
FROM TABLE my_table 
WHERE id IN (SELECT id 
         FROM TABLE my_table 
         WHERE value = input
         )

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35154

Try the following, where you replace 'sss' with the value you are searching for:

select * from table t1
where t1.id in (select id from table t2 where value = 'sss')

Note that value seems not to be a key, such that you might get two different groups of ids in your result. That's also the reason why I proposed an t1.id IN (select id ... rather than an t1.id = (select id ....

Upvotes: 1

Related Questions