CODEWITHSUNDEEP
sql
user2330382
user2330382

Reputation: 19

SQL - Check if employee FirstName and/or LastName exists

Given below tables, check if either columns in Table 1 exists in Table 2 -

Table_1:

FULL_NAME
Crystal, Crystal
Carmen, TEST2
XYZ, ABC
BLA, VVV

Table_2:

NAME
Crystal
Carmen
ZZZ
AAA
VVV

Output:

FULL_NAME STATUS
Crystal, Crystal TRUE
Carmen, TEST2 TRUE
XYZ, ABC FALSE
BLA, VVV TRUE

Upvotes: 1

Views: 166

Answers (2)

Antonio Leonardo
Antonio Leonardo

Reputation: 1862

--Suported on MSSQL, MSACCESS, Oracle, MySQL, PostgreeSQL
SELECT t1.FULL_NAME,
CASE
    WHEN Temp.CONTAINING IS NULL THEN 'FALSE'
    WHEN Temp.CONTAINING IS NOT NULL THEN 'TRUE'
END AS 'STATUS'

FROM Table_1 t1
LEFT JOIN 
    (SELECT NAME, 
        (SELECT FULL_NAME
            FROM Table_1
            WHERE FULL_NAME LIKE CONCAT('%',NAME,'%')) as 'CONTAINING'
    FROM Table_2) Temp ON
t1.FULL_NAME = Temp.CONTAINING

Upvotes: 1

Ajax1234
Ajax1234

Reputation: 71471

You can use exists with instr:

select t1.full_name, exists (select 1 from table_2 t2 where instr(t1.full_name, t2.name) > 0) status from table_1 t1;

Output:

full_name status
Crystal, Crystal 1
Carmen, TEST2 1
XYZ, ABC 0
BLA, VVV 1

Edit: the solution above will match any occurrence of substrings, however, if you want the match to be based solely on the comma separated values, you can use a recursive cte to get the substrings:

with recursive vals(id, n) as (select row_number() over (order by (select 1)), t.* from table_1 t),
     cte(id, v, r) as (
         select id, case when instr(n, ", ") > 0 then substr(n, 1, instr(n, ", ")-1) else n end, case when instr(n, ", ") > 0 then substr(n, instr(n, ", ")+2, length(n) - instr(n, ", ")) else "" end from vals
         union all
         select id, case when instr(r, ", ") > 0 then substr(r, 1, instr(r, ", ")-1) else r end, case when instr(r, ", ") > 0 then substr(r, instr(r, ", ")+2, length(r) - instr(r, ", ")-1) else "" end from cte where length(r) > 0
)
select s1.n, s3.c from (select s.id, max(s.v in (select * from table_2)) c from cte s group by s.id) s3 join vals s1 on s3.id = s1.id;

See demo.

Upvotes: 1

Related Questions