Need to figure out when and ID have two different codes associated in SQL

Question

Hi I need some help with this in SQL.

I have a query like this

select sub.*
from(
    select 
        distinct columnA,   
        case
            'some logic that is working'
        end as ColumnB,
        columnC,    
        "ColumnD"
    from Table1
    where "ColumnD"::date = 'some date'
    UNION
    select 
        distinct columnA,   
        case
            'some logic that is working'
        end as ColumnB,
        columnC,    
        "ColumnD"
    from Table1
    where "ColumnD"::date = 'some date'
    ) sub
order by sub.columnC

                 Result 
   Column A  Column B  Column C  Column D  
       abc      old       a 
       abc      old       a
       jhk      old       b
       1ab2     new       b
       25sa     new       c
       24sb     new       d
       ujy      old       e
       45wr     new       e 

Now in the column A we have code associated to a customer, those codes changed from numerics to alphanumerics. So I need create another query to work in this result (the result of the firt query), to identify all the customers who migrated from old codes to new codes. The output need to be something like this

    oldCode  currentCode  oldType  newType
      jhk       1ab2        old      new 
      ujy       45wr        old      new  

Thanks

Answer 1

You can use SELF JOIN on a resulted table to get the expected result.

Also, You can use the tamporary table to store the result of the complex query else for the simple query you can use your first query directly in FROM clause of the second query.

CREATE TEMPORARY TABLE test
SELECT * FROM tblname;   //this line you can write your first query 

CREATE TEMPORARY TABLE test_copy LIKE test;
INSERT INTO test_copy (SELECT * FROM test);

SELECT 
    t.ColumnA as oldCode,
    t1.ColumnA as newCode,
    t.ColumnB as oldType,
    t1.ColumnB as newType
FROM 
    test as t JOIN test_copy as t1
    ON t.ColumnC=t1.ColumnC
WHERE 
    t.ColumnB="old" AND t1.ColumnB="New";

DEMO