MySQL outer join

Question

I have 2 tables for which I need to run a query on

Table 1 has 2 fields: l_id and name

Table 2 also has 2 fields: l_id and b_id

I need to run a query to get "name" and "l_id" for all entries in table1 that do not have an entry in table2 for a given b_id.

Hope this makes sense

Answer 1

select t1.* from Table1 t1 left outer join Table2 t2 on t1.l_id = t2.l_id and t2.b_id = @SomeValue where t2.l_id is null 

Answer 2

You can use an outer join, but I find the subquery to be a bit simpler. In your case, select everything from table1 in which there is no identifier in table2. Reads better ...

 SELECT * FROM table1 WHERE l_id NOT IN (SELECT l_id FROM table2);