SQL Alias of joined 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 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

Solution 1

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

A right outer join does the exact same thing as a left outer join, with just the tables switched. You can filter on the join and it will still include the data from the initial table.

Solution 2

Add the where clause to the subquery like this:

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)

Comments

[Lincecum]

See above. Since this is an outer join, this won't work. It won't actually exclude all the stuff being pulled from table2.

[JNK]

Is there another reason you need to do a RIGHT OUTER JOIN on this instead of left or inner? In most cases a RIGHT JOIN is pretty much never called for.

[Lincecum]

Not really, as long as it's an outer join

[JNK]

Make it a LEFT OUTER JOIN and problem solved, unless you need orphan records from your table2.

[Lincecum]

I did want all records from table2 initially, until I realized it doesn't really make sense in this scenario. Still, I feel like there might be a scenario where I would want to check constraints after performing an outer join, and I don't think any of the solutions solve that. Maybe that scenario just doesn't exist though.

[JNK]

@Lincecum - I think those two criteria cancel each other out. If you do an outer join, then you want the data without a match in the other table. If you do an inner join you don't want it.

[Lincecum]

Check my edit to the original question. What if I want everything where status!=1, including all data with no corresponding row in table1?

[JNK]

@Lincecum - see edit to the answer. You just need to change the query I posted initially to use an inequality <> in the where clause of the subselect.

[Lincecum]

But this query would still fetch everything from table2 even if its ID corresponds to one with a status not equal to 1 in table2. I don't want the data from table2 to be fetched if its ID has a corresponding entry in table1 with a status of 1.

[Lincecum]

Sorry, I meant "even if its ID corresponds to one with a status THAT IS equal to 1."