mySQL outer join

Question

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

Table1 has 2 fields: l_id, and name

Table2 also has 2 fields: l_id, and b_id

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

Hope this makes some sense

D'Arcy Rittich · Accepted Answer · 2010-10-12 20:25:43Z

10

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

answered Oct 12, 2010 at 20:25

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jason McCreary · Accepted Answer · 2010-10-12 20:26:34Z

3

You can use an outer join, but I find a sub-query is a little more straightforward. In your case selecting everything from table1 that does not have an id in table2. Reads better...

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

answered Oct 12, 2010 at 20:26

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

2 Comments

Señor Reginold Francis Over a year ago

this works for me, I just modified that second query to add the b_id condition, but to be fair, RedFilter answered first

Jason McCreary Over a year ago

No worries. I missed the b_id part anyway.

Collectives™ on Stack Overflow

mySQL outer join

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related