I have 2 tables agreements & party. In table party, when indicator = 1 then party.c1 = agreements.c1 and party.b1 = agreements.b1; when indicator = 0 then party.c1 = agreements.c2 and party.b1 = agreements.b2. The values in agreements table are unique with all 4 columns combined.
agreements
c1 b1 c2 b2
1001 1 2001 1
1001 1 2001 2
1001 1 2002 1
1001 1 2003 1
1001 2 2004 1
1001 2 2004 5
party
c1 b1 indicator id
1001 1 1 id-1
1001 2 1 id-2
2001 1 0 id-3
2001 2 0 id-4
2002 1 0 id-5
2003 1 0 id-6
2004 1 0 id-7
2004 5 0 id-8
I would like to form a row similar to agreements table for every combination of columns (c1, b1) and (c2, b2). In agreements table, (1001,1) is combined with (2001,1) as well as (2001,2), (2002,1) & (2003,1). Similarly, (1001,2) is combined with (2004,1) & (2004,5). So the target output is like this
c1 b1 indicator id c1 b1 indicator id
1001 1 1 id-1 2001 1 0 id-3
1001 1 1 id-1 2001 2 0 id-4
1001 1 1 id-1 2002 1 0 id-5
1001 1 1 id-1 2003 1 0 id-6
1001 2 1 id-2 2004 1 0 id-7
1001 2 1 id-2 2004 5 0 id-8
How can I achieve this, am using postgresql 12.
Update 1: Updated sample data
TIA,
AK
I think this does it. (not tested)
select left_party.*
, right_party.*
from party left_party
join agreements on left_party.c1 = agreements.c1 and left_party.b1 = agreements.b1
join party right_party on agreements.c2 = right_party.c1 and agreements.b2 = right_party.c1