request for help with a SELECT query

[kaash1]

I need a help with selecting the rows in a table depending on the status in previous table.

Table1

Column1 	Column2		 Status
Smith	      Alex	                1
Smith         Mark		        0
John	      Smith		        1

 

 

I have second table with 2 columns. I want to select only those rows which have ANY of the user of table1 in column1 or column2 with status 1.

 

Table2

Column1		Column2		
Smith		  Anderson
Martin		  Walker
Alex		  Scott	

 

For example, Table1, the first row has status 1. Now i want to SELECT the rows from table2, which have "smith" OR "alex" in Column1 OR Column2. So, from Table2 it should select Row1 and Row3.

 

Do I have to join the tables? Wont that be slow?

Can I perform SELECT with EXISTS query?

 

Any help will be highly appreciated.

[fenway]

Yes, you need to JOIN the tables -- the speed will be depending on the indexes you choose and your version of mysql.  Easiest way to handle this is simply to UNION the various options:

 

( select * from table2 inner join table1 on ( table1.column1 = table2.column1 ) where table1.status = 1 )
UNION DISTINCT 
( select * from table2 inner join table1 on ( table1.column1 = table2.column2 ) where table1.status = 1 )
UNION DISTINCT 
( select * from table2 inner join table1 on ( table1.column2 = table2.column1 ) where table1.status = 1 )
UNION DISTINCT 
( select * from table2 inner join table1 on ( table1.column2 = table2.column2 ) where table1.status = 1 )

 

Of course, this makes me question your design.