I am trying to solve the following problem. I have a certain column, to_state, that can take a finite set of values. I want to make sure that my query always returns those sets of finite values, even if not all of the values are represented in the results. In essence, I am looking to expand the result returned by the query by using a right join.
When I do the following simple query, this seems to work. create table hat (the_hat integer); insert into hat values (1),(2),(3); create table cat (the_hat integer); insert into cat values (2),(3),(4),(5); select the_hat from hat right join cat c using(the_hat) The right join changes the results query from 1,2,3 to 2,3,4,5. However, when I use this on my actual query, I don't get any additional rows by adding the right join. select to_state from deal_trans_info right join (select distinct to_state from deal_trans_info where deal = '02FF1') foo using(to_state) where deal = '02FF1' and pool_id = 'C2V' and coll_type = 'ARM 228' and from_state = 'CBNA' and "month" = '20090701' I can't send the actual data since it is too big, but it seems like this is analogous to the simple query above. If anyone has any suggestions, that would be great. Thanks, Andrew
