TransWikia.com

SQL union seems to fail for me

Stack Overflow Asked by Sasha Peric on November 19, 2020

I haven’t done any SQL queries in years.
I ran into an issue that looks to me pretty straightforward, but I cannot seem to solve it in reasonable time.
Let’s say that I have two tables that I pull from the database using the select statement:

    table1:
    id name    status
    1  record1 ACTIVE
    2  record2 ACTIVE

    table2:
    id name    status
    1  record3 DELETED
    2  record1 DELETED

I would like to have the third table that looks like this:

    table3:
    id name    status
    1  record1 ACTIVE
    2  record2 ACTIVE
    3  record3 DELETED

So basically, if the column is present in the "ACTIVE" table, it must be shown in the third one (table3) as ACTIVE regardless of the column that has the same name in "DELETED" table. Also, if the column with specific name is present in the "DELETED" table, but not in the "ACTIVE", it should be shown too as DELETED.

I hope I’ve explained the issue clearly enough.
It’s my first time posting on stackoverflow, and I hope I’ll get some help from you guys.
Thanks! 🙂

One Answer

I think you want:

select a.*
from actives a
union all
select d.*
from deletes d
where not exists (select 1 from actives a where a.name = d.name);

Correct answer by Gordon Linoff on November 19, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP