mysql join two tables

Question

Hi
I have a problem joining two tables:

table1    
id  name  
1   aaa  
2   bbb  
3   ccc  

table2    
id table1_id name  
1 1          x1  
2 1          x2  
3 2          s1 

table1 is the main table, table2 contains attributes.

I need to join and search both tables, but display distinct results from first table.

When using JOIN I get multiple results from table2.

The scenario is I need to search main table TABLE1 and ALL ATTRIBUTES in TABLE2 and return if found

Answer 1

select distinct(name) from table1 inner join table2 on table1.id = table2.table1_id where table2.name = x2;

Should do the trick.

Answer 2

If you need entries which exists in both tables:

 SELECT * from Table1 t1
 WHERE YourConditionsHere
 AND EXISTS (SELECT 1 from Table2 t2
             WHERE t1.Id = t2.Table1_id 
               AND YourConditionsHere)

if you need entries from Table1 for which does not exists enteries in Table2

 SELECT * from Table1 t1 
 LEFT JOIN 
 (SELECT * from Table2 
  WHERE YourConditionsHere
 ) t2
 ON (t1.Id = t2.Table1_id) 
 WHERE YourConditionsHereForTable1

Answer 3

another option

select * from table1 t1 where t1.id in (select table1_id from table2 t2 where t2.name = "x1");

it's probably best to check query plains (i.e. EXPLAIN) for all suggested queries and check the one that performs best for your exact scenario.