postgres join on same table

Question

I have a table that looks like this

years  type     value    x      y
1      b        3.74637  false  true
1      b        -0.52816 true   false
1      mon1     0        true   false
1      mon1     0        false  true
1      mon10    0.00413  true   false
1      mon10    0.00137  false  true

I want the table to look like

years  type     x        y
1      b        3.74637  -0.52816
1      mon1     0        0
1      mon10    0.00413  0.00137

therefore I create a request where I join the table on itself

SELECT 
     i.years, 
     i.type, 
     i.value as b, 
     j.value as m 
from abc as i 
inner join abc as j on i.type = j.type AND i.years = j.years 
WHERE i.type = j.type AND i.m = j.b

now I get

years   type    x        y
1       b       3.74637  -0.52816
1       b       -0.52816 3.74637
1       mon1    0        0
1       mon1    0        0
1       mon10   0.00413  0.00137
1       mon10   0.00137  0.00413

how do I get rid of the doublets where the x value of a line is equal to the y of the next line

if x it true then y is false and vise versa, but thats redundant and was originally a hint of where the values came from, but I dont care were both values come from cause I need x AND y to calcualte, so I want x and y with the same type and year as a result — aldr
– aldr, Commented Mar 24, 2017 at 14:10

Michael Hibay · Accepted Answer · 2017-03-24 15:17:47Z

7

You don't need to do anything extra but add some additional constraints on the join. You really don't want to do the sub queries as there is a performance hit for no reason.

 SELECT 
   i.years, 
   i.type, 
   i.value as b, 
   j.value as m 
 from abc i
 inner join abc j on i.type = j.type and i.x = true and j.y = true;

answered Mar 24, 2017 at 15:17

Michael Hibay

5323 silver badges11 bronze badges

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

Comments

Gurwinder Singh · Accepted Answer · 2017-03-24 14:10:43Z

4

You can use aggregation:

Select years, type,
   Max (case when x then value end)as x,
   Max (case when y then value end)as y
From t
Group by years, type

answered Mar 24, 2017 at 14:10

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Comments

user330315 · Accepted Answer · 2017-03-24 14:14:44Z

2

Assuming x and y are real boolean columns:

select xv.years, xv.type, xv.value as x, yv.value as y
from abc xv
   join abc yv on (xv.years, xv.type) = (yv.years, yv.type) and yv.y
where xv.x;

Online example: http://rextester.com/TUQPQH27415

answered Mar 24, 2017 at 14:14

user330315

Comments

JohnHC · Accepted Answer · 2017-03-24 14:09:20Z

1

Subqueries:

select x1.*, y2.y
from 
(
    select years, type, value as x
    from MyTable
    where x = 'true'
) x1
left join
(
    select years, type, value as y
    from MyTable
    where y = 'true'
) y2
on x1.years = y2.years
and x1.type = y2.type

answered Mar 24, 2017 at 14:09

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

2 Comments

aldr Over a year ago

I accepted your answer cause it looks my way of thinking, but the answer of GurV is very elegant and smart

Michael Hibay Over a year ago

No need for a subquery, please don't use this in your code. Look at my answer for a better solution.

Collectives™ on Stack Overflow

postgres join on same table

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related