-2

enter image description here

Hi

the column named Diagnosis is my first table (Table_A) and the column from Claim_NO is my second table (Table_B).

I want to return all the records from the second table (Table_B) where the the diagnosis is there in Table_A.

the script

Select * from Table_B b where b.diagnosis in(select * from Table_A)

will work, but in the diagnosis column there are some cases where there are two diagnosis separated by a coma.

How do I return the blue highlighted records

ysth
  • 96,171
  • 6
  • 121
  • 214
Lehlohonolo Mapitsi
  • 1
  • 1
  • 2
    Welcome to Stack Overflow. Please take the [tour] to learn how Stack Overflow works and read [ask] on how to improve the quality of your question. Then see https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 for SQL related questions. – Progman Jul 12 '21 at 19:18
  • What does `select version();` show? – ysth Jul 12 '21 at 23:09
  • please edit your question to show (as text, not an image) output of `show create table Table_B;` and `select version();` – ysth Jul 13 '21 at 00:54

1 Answers1

0

So with updated question, I see Table_A is your lookup table. Use FIND_IN_SET(,) to get the position of first argument in the second csv string argument

Hence the query would be like

SELECT * from Table_B b
JOIN Table_A a on FIND_IN_SET(a.Diagnosis,b.Diagnosis) > 0

This is inefficient as this query will scan Diagnosis column of Table_A in the comma separated value in Diagnosis column of Table_B and get records of B where Diagnosis matches any of that in A

Highly encourage to read this answer for more insight

Yogesh Kurane
  • 37
  • 6