mysql complex query help please

Question

1. I want to select all rows from Table 1 WHERE there is smith in column1 or column2 AND status 1.

2. IF there is smith in column 1, then select the value from column 2, and if there is smith in column2, then select value in column 1 of the row.

3. Then select all the rows from Table 2 WHICH contains that value in column1 or column2 of table 2, (which we got by selecting from Table 1.)

Answer 1

This should cover #1/2, you've lost me with #3

select if(col1 = 'smith', col2, col1) from table1 
where (col1 = 'smith' or col2 = 'smith') and status = 1

Answer 2

with Ben's query

select * from table2
where column1 in (select if(column1 = 'smith', column2, column1) from table1 where (column1 = 'smith' or column2 = 'smith') and status = 1)
OR
column2 in (select if(column1 = 'smith', column2, column1) from table1 where (column1 = 'smith' or column2 = 'smith') and status = 1)

OR

select * from table2 where
column1 in (select column1 from table1 where column2 = 'Smith' AND status = 1) OR 
column1 in (select column2 from table1 where column1 = 'Smith' AND status = 1) OR 
column2 in (select column1 from table1 where column2 = 'Smith' AND status = 1) OR 
column2 in (select column2 from table1 where column1 = 'Smith' AND status = 1)

Answer 3

select * 
  from  table1 as t1, table2 as t2
 where t1.status = 1
   and (t1.col1 = 'smith' and (t2.col1 = t1.col2 or t2.col2 = t1.col2)
       or t1.col2 = 'smith' and (t2.col1 = t1.col1 or t2.col2 = t1.col1))

Answer 4

Probably the best sollution would be to redesign your database, but if you really want to keep your tables, you can try this query:

SELECT IF(t1.col1 = 'smith', t1.col2, t1.col1) AS t1col2,  IF(t2.col1 = t1col2, t2.col2, t2.col1) AS t2col2
FROM table1 AS t1
JOIN table2 AS t2 ON( IF(t1.col1 = 'smith', t1.col2, t1.col1) IN ( t2.col1, t2.col2 ) )
WHERE (t1.col1 = 'smith' OR t1.col2 = 'smith') AND t1.status = 1

Answer 5

Something like this might be what you are after...

SELECT *
    FROM table_1
    WHERE col_1 LIKE '%smith%' AND status = 1

UNION DISTINCT

SELECT *
    FROM table_1
    WHERE col_2 LIKE '%smith%' AND status = 1

UNION

    SELECT *
        FROM table_2
        WHERE ...er...

At this point I stop being able to understand the question, as the return from Question 1 and Question 2 isn't a value but a result set. But it could continue along the lines of...

WHERE col_1 IN (SELECT col_1
                    FROM table_1
                    WHERE col_1 LIKE '%smith%' AND status = 1
                  UNION
                SELECT col_2
                    FROM table_1
                    WHERE col_2 LIKE '%smith%' AND status = 1);