help me in mysql query

Question

look here and here

With the of your answers above, I have made this query, can you please suggest if it is valid ? (If Not, can you please correct it? )

SELECT *,
 FROM TABLE_2 t
 WHERE EXISTS(SELECT IF(column1 = 'smith', column2, column1)       
 FROM TABLE_1 a
 WHERE 'smith' IN (a.column1, a.column2)
        AND a.status = 1              
                AND ( 'smith' IN (t.column1, t.column2)
               )

Answer 1

To start with, the comma after select * does not belong.

Second, you alias your tables (table_2 t and table_1 a), but then you don't consistently use the aliases, so you might have issues at run time. Also from a maintenance perspective, I think most folks prefer to use aliases when declared, and no aliases otherwise.

Third, you do a comparison against cols from the t table in the outer select ('smith' in (t.column1, t.column2) ), when that appears unnecessary. You can just do it in the outer select. In other words, you can move that terminal paren to before the AND ('smith'...

As for whether it works -- I have no idea, since I don't know what you are trying to accomplish.

Combined, that would leave you with :

SELECT t.*
FROM TABLE_2 t
WHERE EXISTS (SELECT IF(a.column1 = 'smith', a.column2, a.column1)       
              FROM TABLE_1 a
              WHERE 'smith' IN (a.column1, a.column2)
              AND a.status = 1)
AND ( 'smith' IN (t.column1, t.column2)