CODEWITHSUNDEEP
sqlpostgresqlselectsubquery
pinoyyid
pinoyyid

Reputation: 22306

how do I make a query which references other rows in a resultset?

The question is best asked with an example.

I have a table

id   |    name      |   attr
1    |     foo      |     a
2    |     bar      |     a    
3    |     baz      |     b

and I want a query give me all the rows which share the same attr as 'name==foo', and thus returns

id   |    name      |   attr
1    |     foo      |     a
2    |     bar      |     a

because foo has attr=a, as does bar

Upvotes: 1

Views: 27

Answers (2)

GMB
GMB

Reputation: 222622

You can use exists:

select t.*
from mytable t
where exists (
    select 1 from mytable t1 where t1.attr = t.attr and t1.name = 'foo'
)

Note that this solution would also properly work if 'foo' had more than one attribute.

For performance, you want an index on (attr, name).

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

A simple way is a correlated subquery:

select t.*
from t
where t.attr = (select t2.attr from t t2 where t.name = 'foo');

Upvotes: 1

Related Questions