I wanted to find matching records from the column of a table1 to the other column in table2. It has to compare and if it matches then produce output of values from Table1.

And table2

Output needed something like this:

select
    t2.column a,
    case when exists (select * from table1 t1 where t1.column b in (t2.column a )
        then 'Matched'
        else 'Unmatched'
    end matched
from table2 t2

CodePudding user response：

Just use an INNER JOIN:

SELECT table1.columnB
FROM table1
INNER JOIN table2 ON table2.columnA = table1.columnB

see: DBFIDDLE

P.S. Next time do not provide output like "needed something like this", but provide expected output based on the given input. (This is why the DbFiddle does not use the exact input data that is given in the question)

EDIT: Because " if the first 5 digits matches with the table2 column values then I would need that also to be displayed. "

SELECT
   columnB as result
FROM table1
CROSS JOIN table2 ON LEFT(table2.columnA,LEAST(length(columnB),length(columnA))) = 
                     LEFT(table1.columnB,LEAST(length(columnB),length(columnA)))

see last part of DBFIDDLE

LEAST will returns the smallest (minimum-valued) argument, so only the first part of the values in both column will be compared, and MySQL will automatically convert then to a string before taking the LEFT part of it.