I need to obtain a single record from 2 records in the same table
grouping for a key, say
id value value2
1 2 5
1 2 7
the result would be
1 2 5 7
and that works fine with a full join:
SELECT * FROM
(SELECT idp, data, i1, i2, m1, m2 from tblwk WHERE recordkey = 4) s1
FULL JOIN
(SELECT idp, data, i1, m1, m2 from tblwk WHERE recordkey = 10) s2
USING (data, idp)
Now, to get another dataset, I need the second subselect to change based
on a value acquired from the first one. I tried with a case
SELECT * FROM (
SELECT idp, data, idd, rif1, rif2, t1, t2, t3, t5, t7, t9, t10, i1,
i2, i3, dg from tblwk WHERE recordkey = 1) s1
FULL JOIN
case
when i1=1 then (SELECT idp, data, desc, rif1, rif3, t1, t2,
t5 from tblwk WHERE recordkey = 2) s2
when i1=2 then (SELECT idp, data, desc, rif1, t4, t5, i2
from tblwk WHERE recordkey = 3 order by i2) s2
when i1=3 then (SELECT idp, data, desc, rif1, t2, t5, t6, i2
from tblwk WHERE recordkey = 4 order by i2) s2
when i1=4 then (SELECT idp, data, desc, i2 from tblwk WHERE
recordkey = 9) s2
end
USING (data, idp)
but it doesn't like "case" after a FULL JOIN.
I read a bit of docs and discovered LATERAL, but AFAIK it's useless here.
Is it necessary to write a function (which would be my last resort, not
just because I'm not so good in writing functions) or there is some SQL
syntax that can come in help?
Thanks
Moreno.-