Joining Tables in PostgreSQL

Question

I am having trouble getting the data into a single table from multiple tables using PostgreSQL. I have the following two tables:

schedule.event:

 | starttime  | endtime    | hash |
 | 1612716238 | 1612716266 | xyz  |
 | 1612716912 | 1612718972 | abc  |

schedule.resource:

 | name     | type      | hash |
 | sat1     | satellite | xyz  |
 | station1 | station   | xyz  |
 | modem1   | modem     | xyz  |
 | sat2     | satellite | abc  |
 | station1 | station   | abc  |
 | modem1   | modem     | abc  |

I want to get the data in the following table format:

 | starttime  | endtime    | satellite | station  | modem  |
 | 1612716238 | 1612716266 | sat1      | station1 | modem1 |
 | 1612716912 | 1612718972 | sat2      | station1 | modem1 |

I am having trouble writing the PostgreSql query to do that.

Thanks

Popeye · Accepted Answer · 2021-02-07 17:07:57Z

1

You need conditional aggregation as follows:

Select t1.starttime, t1.endtime, t1.hash,
       Max(case when t2.type = 'satelite' then name end) as satelite,
       Max(case when t2.type = 'station' then name end) as station,
       Max(case when t2.type = 'modem' then name end) as modem
  From event t1
  Join resource t2 on t1.hash = t2.hash
  Group by t1.starttime, t1.endtime, t1.hash;

answered Feb 7, 2021 at 17:07

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

Alternatily: max(name) filter (where t2.type = 'satellite')

Popeye Over a year ago

Yeap, that is also a good option but I thought to give answer using standard sql. Thanks @a_horse_with_no_name for adding alternative approach/synatx.

Gordon Linoff Over a year ago

@Popeye . . . FILTER is standard SQL.

Collectives™ on Stack Overflow

Joining Tables in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related