CODEWITHSUNDEEP
sqlsql-serverjoinaliasouter-join
Lincecum
Lincecum

Reputation: 825

SQL Alias of joined tables

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

Upvotes: 9

Views: 106900

Answers (3)

JNK
JNK

Reputation: 65147

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)

That should work since you will get all the table2 data regardless.

EDIT 2:

OK, to get everything from table2 EXCEPT where there is a status ID in table 1, even if there is not an entry in table1, you need to use the EXCEPT function, which will basically exclude a subset from a larger dataset.

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38503

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.

Upvotes: 15

Samuel Neff
Samuel Neff

Reputation: 74899

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)

Upvotes: 3

Related Questions