I have a query like this:
select a1.name, b1.info
from (select name, id, status
from table1 a) as a1
right outer join (select id, info
from table2 b) as b1 on (a1.id = b1.id)
I only want to include everything where a1.status=1 and since I'm using an outer join, I can't just add a where
constraint to table1, because all info from table2 that I want to be excluded will still be there, just without the name. I was thinking something like this:
select z1.name, z1.info
from ((select name, id, status
from table1 a) as a1
right outer join (select id, info
from table2 b) as b1 on (a1.id = b1.id)) as z1
where z1.status = 1
but I don't think that's legal.
EDIT: As described below, an outer join actually doesn't make sense for what I'm trying to do. What if, for example, I want all the data from table2 where status!=1 in table1, inclusive of all data where a corresponding ID does not at all exist in table1. Thus I would need an outer join of all data from table2, but still want to exclude those entries where the status=1.
Equivalent to this:
select z1.name, z1.info
from ((select name, id, status
from table1 a) as a1
right outer join (select id, info
from table2 b) as b1 on (a1.id = b1.id)) as z1
where z1.status != 1