SQL Alias ​​of Join Tables

Question

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 want to include everything where a1.status = 1, and since I am using an external join, I cannot just add the where constraint to table1, because all the information from table2 that I want to exclude will still be there without a name. I thought 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 do not consider it legal.

EDIT: As described below, an outer join doesn't actually 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, including all the data where the corresponding identifier does not exist at all in table 1. Thus, I need an outer join of all the data from table2, but still you want to exclude those entries where 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 

Answer 1

 SELECT a1.Name, b1.Info FROM table2 b1 JOIN table2 a1 ON b1.id= a1.id AND a1.status = 1 

A direct outer join does the same as a left outer join, with only the tables switched. You can filter the join and it will still include data from the source table.

Answer 2

Add the where clause to the subquery as follows:

 select a1.name, b1.info from ( select name, id from table1 a where a.status = 1 ) as a1 right outer join ( select id, info from table2 b ) as b1 on (a1.id=b1.id) 

Answer 3

 select a1.name, b1.info from (select name, id, status from table1 a WHERE status=1) as a1 right outer join (select id, info from table2 b) as b1 on (a1.id=b1.id) 

EDIT:

For your second scenario:

 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) EXCEPT select a1.name, b1.info from (select name, id, status from table1 a WHERE status<>1) as a1 right outer join (select id, info from table2 b) as b1 on (a1.id=b1.id) 

This should work, as you will get all the data in table2 independently.

EDIT 2:

OK, to get everything from table2 EXCEPT, where table 1 has a status identifier, even if there is no record in table 1, you need to use the EXCEPT function, which basically excludes a subset of the larger data set.