Question 176114 in PostgreSQL Question posted in
The official documentation can be found here.

“How do I write a Postgresql SQL query to return entries with the same column value from the same table?”

I have more or less the following table:

list_id key
1 a
1 b
1 c
2 d
2 e
3 f
3 g
… …

Given a certain key or set of keys, what sql query will return all the keys that share a given list_id. For example, given key a, I want to return a, b, c. Given a,d, I should get back a,b,c,d,e.

Tags:

2

Answers


  1. 0

    On option is to select the list_ids you want in a subquery / cte and from there filter the rest, e.g.

    WITH j (id) AS (
  SELECT DISTINCT list_id FROM t
  WHERE key IN ('a','d')
)
SELECT key FROM t
JOIN j ON j.id = t.list_id;

    Demo: db<>fiddle

    Login or Signup to reply.
  2. 0

    You could use exists:

    select key
from mytable t
where exists (
    select 1 from mytable t1 where t1.key in ('a', 'd')  and t1.list_id = t.list_id
)

    If two keys might match on more than one list_id, then you would probably need select distinct key to avoid duplicates in the resultset (although that’s not the case for your example data).

    This query would take likely advantage of an index on mytable(key, list_id) (or mytable(list_id, key) depending on the cardinality of the two columns).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search