How to join two tables on changed columns?
I have two tables in PostgreSQL:
A (ida, ida2) B (idb, idb2)
all columns are integer. I need to join them in an non-standard join, meaning that if ida2 is not null then the join is on (ida2=idb2) if it's null the join is on (ida=idb)
I need to use it in a view so I can not use IF statment. I thought of using coalesce, but it can only select 1 integer not 2.
CREATE OR REPLACE VIEW myview AS SELECT ..... FROM A join B on .....? ;
How do I do that?
CREATE OR REPLACE VIEW myview AS SELECT ..... FROM A join B on ida2 is not null and ida2 = idb2 UNION SELECT ..... FROM A join B on ida2 is null and ida = idb