CODEWITHSUNDEEP
sqlpostgresql
tristantzara
tristantzara

Reputation: 5917

select join result as columns to same row

I have a one-to-many relation in a pg database. I have table A and table B, where rows of B have a foreign key to A.

I want to select certain rows from A and attach certain columns from matching rows of B to same row from A.

E.g.

      A
id | created_at |

      B
id | created_at | a_id | type |

I tried to do multiple subqueries, e.g.

select A.id,
(select created_at from B where b.a_id = a.id and B.type = 'some_type' limit 1) as some_type_created_at,
(select created_at from B where b.a_id = a.id and B.type = 'another_type' limit 1) as another_type_created_at
from A

But this is obviously ugly and wrong, feels like that. What is the better way of achieving it in Postgres?

Ofcourse I can do join and get the full cartesian product, but I want the result from the db to be directly like this.

Upvotes: 0

Views: 46

Answers (1)

The Impaler
The Impaler

Reputation: 48865

There's nothing wrong about using scalar subqueries the way you are doing it. That will work well and will give you the result you want.

Alternatively, you could use lateral table expressions; that will also give you the same result, it's more complex, and in this case I don't see any particular benefit to use them. Lateral queries will take the form:

select
  a.id,
  b1.created_at as some_type_created_at,
  b2.created_at as another_type_created_at
from a
left join lateral (
  select created_at from B where b.a_id = a.id and B.type = 'some_type' limit 1
) b1 on true,
left join lateral (
  select created_at from B where b.a_id = a.id and B.type = 'another_type' limit 1
) b2 on true

In sum, you are good as you are.

Upvotes: 1

Related Questions