How can I select the value from one of two columns, where the value is contained in another table?

Source Table:

Table to check entries against:

Resulting query:

The fourth entry of the source table (Computer/ABCD123) is not valid, because neither "Computer" nor "ABCD123" exists within the checking table.

I tried:

Select 
Switch(
       DataA in (SELECT DataC FROM CheckTable), DataA, --if DataA is found in DataC, select DataA
       DataB in (SELECT DataC FROM CheckTable), DataB, --if DataB is found in DataC, select DataB
      ) AS Result --return variable 'Result'
FROM SourceTable;

without success.

CodePudding user response：

Try:

select distinct DataC as result 
from check_tbl 
where DataC in (select distinct DataA 
                from source_tbl) 
or DataC in ( select distinct DataB 
              from source_tbl)

Data Examples:

create table source_tbl(
DataA varchar(30),
DataB varchar (30) );

insert into source_tbl values 
                     ('Apple','Apple'),
                     ('Orange','Ora'),
                     ('Pinea','Pineapple'),
                     ('Computer','ABCD123');

create table check_tbl(
DataC varchar(30)    );

insert into check_tbl values 
                      ('Apple'),
                      ('Orange'),
                      ('Pineapple');

Result:

result
Apple
Orange
Pineapple

Demo:https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8b0a13a07ae245b00616bb24b0a972b0

CodePudding user response：

A simple UNION will do:

SELECT DataA 
FROM Demo1 INNER JOIN DemoCheck ON Demo1.DataA = DemoCheck.DataC;
UNION
SELECT DataB 
FROM Demo1 INNER JOIN DemoCheck ON Demo1.DataB = DemoCheck.DataC;

A UNION retrieves the value only once even if they exist in both parts of the union.