SQL - column value depending on if another column value appears in a third column

Question

I'm trying to create a table where column three value will depends on if the value of column 1 is present in any row of column 2.

Here's what the table I want to create should look like:

doc_name    doc_used    tool
---------------------------------
MEA123     MEA123       tool1      
MEA123     Null         tool1
MEA555     Null         Null
MEA554     Null         Null
MEA123     Null         tool1

But I only manage to get it working for the first line :

doc_name    doc_used    tool
---------------------------------
MEA123     MEA123       tool1      
MEA123     Null         Null
MEA555     Null         Null
MEA555     Null         Null
MEA123     Null         Null

Here's the codes I tried:

CREATE TABLE `/mydataset` AS (
SELECT 
doc_name,
doc_used,
(CASE 
   WHEN doc_name  in  (doc_used) then 'tool1'
END) as tool

FROM  `/test_dct_tool`)

The list of possible doc_used is unknown so I would like to check if value of column doc_name exists in the whole list of values of col doc_used. I've tried to use "exists" and "in (select doc_used from )" but couldn't get anywhere either.

Answer 1

You seem to want:

SELECT doc_name, doc_used,
       (CASE WHEN doc_name IN (SELECT t2.doc_used FROM `/test_dct_tool`) 
             THEN 'tool1'
        END) as tool
FROM `/test_dct_tool` t;