CODEWITHSUNDEEP
sqldatabasesqlite
Anonymous
Anonymous

Reputation: 1373

Compare rows with a column value

I'm trying to figure out how to retrieve all rows with a ID which have a specific value in the other column.

The database looks like this:

Database Illustration

I want to retrieve those rows which have a VAL of 2 and the corresponding ID rows. So in this case this would give me all the ID 1 rows and the ID 3 row:

Database Illustration 2

Upvotes: 1

Views: 75

Answers (3)

Kirill Kin
Kirill Kin

Reputation: 391

Constructions like where id in (select id from ...) and where exists (select 1 from ...) may take very long time, because for every row in test you are executing subquery. To solve your problem you can join test with itself and make distinct like this:

SELECT DISTINCT t1.* 
FROM test t1
     INNER JOIN test t2 ON t1.id = t2.id
WHERE t2.val = 2

Upvotes: 1

john McTighe
john McTighe

Reputation: 1181

you'll need a subquery (or join or cte or derived table) Subquery is easist to visualise

Select * from Test where ID IN (SELECT ID from Test where VAL = 2)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method is exists:

select t.*
from test t
where exists (select 1 from test t2 where t2.id = t.id and t2.val = 2);

Upvotes: 2

Related Questions