Binary Case when in Union Query

Question

I am facing some problems and I got stuck in building this query.

I would like to stack 2 columns from the same table into a long single one (I use UNION statement), and then, I would like to produce a new variable to tell me if the number (stack of column1 and column2, organism_id) comes from column 1 or comes from column 2. For now, I have been trying this approach but I have a problem which I do not understand in the following query:

SELECT u.organism_id, case when u.organism_id IN cpl.column1 then 1
                           else 0
                           end as is_column1
FROM  
    (select column1 as organism_id
    from table1
        UNION
    select column2
    from table1) as u,
    table1 as cpl;

Does someone have a clue on how to solve this problem?

Thanks in advance!

Answer 1

In general, and if I understand you correctly, you can throw a source column on the tables before unioning them. I'd also suggest UNION ALL to avoid accidental removal of duplicates:

SELECT
    *
FROM
    (
    SELECT
        'Column1' AS Source,
        Column1
    FROM
        Table1

    UNION ALL

    SELECT
        'Column2' AS Source,
        Column2
    FROM
        Table1
    ) u