Postgres self join multiple times

Question

Suppose we have this table

No      Date                       Value
1       2018-02-20 12:00:00        200
1       2018-02-20 12:05:00        205
2       2018-02-20 12:00:00        205 
2       2018-02-20 12:05:00        200
3       2018-02-20 12:00:00        205
3       2018-02-20 12:05:00        210

I want to create this

Date                   Value1      Value2       Value3
2018-02-20 12:00:00     200         205          205
2018-02-20 12:05:00     205         200          210

How can I create this? self join?

I tried this

    select p1.VESSEL_NAME,p1.x_LocalTimeStamp_,p1.RealPowerTotal as   "KW1",p1.LF as "LF1",
p2.RealPowerTotal as "KW2",p2.LF as "LF2",
p3.RealPowerTotal as "KW3",p3.LF as "LF3" 
from Kwhmeters5min as p1 
where p1.x_DGNo_='1' and p1.x_LocalTimeStamp_>='2018-02-01 00:00:00' 
inner join Kwhmeters5min as p2 on p2.VESSEL_NAME=p1.VESSEL_NAME and p2.x_LocalTimeStamp_=p1.x_LocalTimeStamp_
where p2.x_DGNo_='2' 
inner join Kwhmeters5min as p3 on p3.VESSEL_NAME=p1.VESSEL_NAME and p3.x_LocalTimeStamp_=p1.x_LocalTimeStamp_
where p3.x_DGNo_='3'

but I get error near "inner".

First you do the joins, THEN you do the WHERE conditions on the whole resultset — Alfabravo, Feb 28 '18 at 15:29
Hi. Read an intro to select statements. An inner & outer join has an on, cross join doesn't, and where happens after all joins. PS Read & act on [mcve]. That includes *exact executable input* and *exact output*. Also always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. Even vague 'sql select syntax stackoverflow join where' gets https://stackoverflow.com/a/20981676/3404097. — philipxy, Mar 01 '18 at 01:47

Pelin · Answer 1 · 2018-02-28T16:07:29.610

You can't add other join after where statement. Below query should work.

SELECT
p1.VESSEL_NAME,
p1.x_LocalTimeStamp_,
p1.RealPowerTotal AS "KW1",
p1.LF AS "LF1",
p2.RealPowerTotal AS "KW2",
p2.LF AS "LF2",
p3.RealPowerTotal AS "KW3",
p3.LF AS "LF3" 
FROM Kwhmeters5min AS p1 INNER JOIN Kwhmeters5min AS p2 ON p2.VESSEL_NAME = p1.VESSEL_NAME
AND p2.x_LocalTimeStamp_ = p1.x_LocalTimeStamp_
INNER JOIN Kwhmeters5min AS p3 ON p3.VESSEL_NAME = p1.VESSEL_NAME
AND p3.x_LocalTimeStamp_ = p1.x_LocalTimeStamp_
WHERE
    p3.x_DGNo_ = '1'
AND p1.x_DGNo_ = '2'
AND p2.x_DGNo_ = '3'
AND p1.x_LocalTimeStamp_ >= '2018-02-01 00:00:00'

score 0 · Answer 2 · answered Feb 28 '18 at 16:00

suppose your table is called public.tbl_test i would do it in postgres this way using a window function:

select distinct on (public.tbl_test.date)
public.tbl_test.date,
nth_value(public.tbl_test.value,1 ) over (PARTITION by public.tbl_test.date order by public.tbl_test.no rows between unbounded preceding and unbounded following) as value1,
nth_value(public.tbl_test.value,2 ) over (PARTITION by public.tbl_test.date order by public.tbl_test.no rows between unbounded preceding and unbounded following) as value2,
nth_value(public.tbl_test.value,3 ) over (PARTITION by public.tbl_test.date order by public.tbl_test.no rows between unbounded preceding and unbounded following) as value3
from public.tbl_test

score 0 · Answer 3 · answered Mar 01 '18 at 10:21

0

try this: select x_LocalTimeStamp_,array_agg(RealPowerTotal) from Kwhmeters5min group by x_LocalTimeStamp_

answered Mar 01 '18 at 10:21

Judit

114
1
3
10

Postgres self join multiple times

3 Answers3