9

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
Lincecum
  • 795
  • 3
  • 8
  • 11
  • I think I realized my problem... an outer join doesn't really make sense for what I'm doing. If the status needs to be 1, that means the record must exist in table1, so I should be using an inner join anyway. – Lincecum Nov 01 '10 at 19:03
  • @Lincecum - In response to some of your comments - I don't think you understand how the `JOIN` s work. A `LEFT OUTER JOIN` shows you all records in your first table, and then any matching records from the second (or `NULL` if there is not a match). A `RIGHT OUTER JOIN` does the opposite - all records from table 2, matching only in table 1. An `INNER JOIN` shows you only where the criteria matches/records exist in BOTH tables. – JNK Nov 01 '10 at 19:03
  • Actually, I understand quite well what a Join does. I was initially fetching all the data from table2 using the right outer join to ensure I got all that data regardless of whether a corresponding ID existed in table1. I have since decided I needed only the data from those who were active, which is indicated by table1's status. I was trying to make this modification without realizing that an outer join becomes pointless if a corresponding record with an active status MUST exist in table1. – Lincecum Nov 01 '10 at 19:15
  • @Lincecum - Super, I didn't mean to be condescending or insulting if I came across that way. Joins are one of the most often misunderstood aspects of SQL, that's all :) – JNK Nov 01 '10 at 19:26
  • No worries, my question didn't exactly make sense logically, so I see how you could take it as my own misunderstanding of the concept. I think my revised question makes sense though. – Lincecum Nov 01 '10 at 19:33
  • @Lincecum - it does, but a where in the subselect will give what you are after. I added to my answer below. You will still be getting `NULL` values for table 1, though. there's no way around that one with an outer join. – JNK Nov 01 '10 at 19:36

3 Answers3

15
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.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
3

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)
Samuel Neff
  • 73,278
  • 17
  • 138
  • 182
0
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.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • See above. Since this is an outer join, this won't work. It won't actually exclude all the stuff being pulled from table2. – Lincecum Nov 01 '10 at 18:39
  • 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. – JNK Nov 01 '10 at 18:43
  • Not really, as long as it's an outer join – Lincecum Nov 01 '10 at 18:49
  • Make it a `LEFT OUTER JOIN` and problem solved, unless you need orphan records from your table2. – JNK Nov 01 '10 at 18:55
  • 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. – Lincecum Nov 01 '10 at 19:18
  • @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. – JNK Nov 01 '10 at 19:25
  • Check my edit to the original question. What if I want everything where status!=1, including all data with no corresponding row in table1? – Lincecum Nov 01 '10 at 19:27
  • @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. – JNK Nov 01 '10 at 19:32
  • 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 Nov 01 '10 at 19:38
  • Sorry, I meant "even if its ID corresponds to one with a status THAT IS equal to 1." – Lincecum Nov 01 '10 at 19:45