Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 815
Lincecum Avatar asked Nov 01 '10 18:11

Lincecum


2 Answers

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.

like image 129
Dustin Laine Avatar answered Sep 26 '22 03:09

Dustin Laine


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)
like image 39
Samuel Neff Avatar answered Sep 24 '22 03:09

Samuel Neff