How to create outer join with specified value on outer table

system · February 28, 2008, 9:15am

This is probably basic SQL but im struggling

Table A is linked to Table B, with an outer join, so that i want all records from Table A but only values from Table B if they exist. However, i only care about records in Table B when field1=1. Once this condition is added, it overwrites the outer join and will now only return rows for Table A where there are corresponding records in table B where field1=1.

I have tried to remedy this by using 2 Subreports as i need records from Table B where field1=1 and records from Table B where field1=2. However, im still only getting Table A records where there is a field1=1 and a field1=2 record.

I really want Table A records regardless, and if there are field1=1 and field1=2 records, then display the values, otherwise show blank fields. (basically Table B holds life details and the value 1 denotes first life and value 2 denotes 2nd life, so a policy may or may not have a 2nd life but at the moment is only showing me policies with joint lifes)

I also dont want to use a command, really want to use database tables linked via database expert as i want to retain my ability to create dynamic parameters which can be manipulated before the SQL is processed.

mathib (BOB member since 2004-10-27)

system · June 17, 2008, 10:10pm

Are you setting your joins in the Database Expert > Links tab?

If I correctly understood your description, you have set a “Left Outer Join”. This works for a one-to-many and a one-to-zero relationship.

You might want to experiment with a “Right Outer Join” setting in which you can retrieve records from Table B regardless of a relationship to records in Table A.

Chamsin (BOB member since 2008-06-05)

system · June 23, 2008, 8:32pm

It looks like this is an old request and probably long since solved but I’ll add a little in case someone else has this type of problem where you have an outer join and you also want to restrict the “outer” table but still get all of the extra records. The trick is you have to do the record restriction prior to the outer join.

I think this is what you want to do but like you said it doesn’t give the “outer” records.


select whatever
  from table1 t1,
       table2 t2
 where t1.id = t2.id (+)
   and t2.field1 = 1;

I believe you will get the correct data if your raise that field1 requirement up into the from statement like this.


select whatever
  from table1 t1,
       (Select id, anythingelse from table2 where field1=1) t2
 where t1.id = t2.id (+)

Unfortunately to do this you need to use a command object which you said you didn’t want to do. In that case if the field1=1 requirement is always the same you can create a view on that table. The new SQL is essentially what my solution above did by bringing the field1=1 logic up into the from before the join takes place. The power of the command object is you can have dynamic SQL. Views are static. Adding the SQL in the from is like a derived tables in a universe.

create view viewwherefield1equals1 as Select id, anythingelse from table2 where field1=1

select whatever
  from table1 t1,
       viewwherefield1equals1 t2
 where t1.id = t2.id (+)

I hope this helps someone.

tbrady (BOB member since 2007-09-25)

system · July 22, 2008, 12:55pm

no idea what i was doing before, but i have retried this using subreports, and it is returning the correct results.

which i think, in effect, each subreport is a “view” as per tbradys post?

mathib (BOB member since 2004-10-27)

system · September 30, 2008, 9:52am

tbrady:

It looks like this is an old request and probably long since solved but I’ll add a little in case someone else has this type of problem where you have an outer join and you also want to restrict the “outer” table but still get all of the extra records. The trick is you have to do the record restriction prior to the outer join.

I think this is what you want to do but like you said it doesn’t give the “outer” records.
select whatever
  from table1 t1,
       table2 t2
 where t1.id = t2.id (+)
   and t2.field1 = 1;
I believe you will get the correct data if your raise that field1 requirement up into the from statement like this.
select whatever
  from table1 t1,
       (Select id, anythingelse from table2 where field1=1) t2
 where t1.id = t2.id (+)
Unfortunately to do this you need to use a command object which you said you didn’t want to do. In that case if the field1=1 requirement is always the same you can create a view on that table. The new SQL is essentially what my solution above did by bringing the field1=1 logic up into the from before the join takes place. The power of the command object is you can have dynamic SQL. Views are static. Adding the SQL in the from is like a derived tables in a universe.
create view viewwherefield1equals1 as Select id, anythingelse from table2 where field1=1

select whatever
  from table1 t1,
       viewwherefield1equals1 t2
 where t1.id = t2.id (+)
I hope this helps someone.

I am trying to achieve something similar, but I have no idea what a command object is. Could you please elaborate? Thanks.

wahey (BOB member since 2007-05-31)

system · October 1, 2008, 8:39pm

When setting up a data source for a report in the database Expert after you get connected to the database it lists all of the table owners. Above all the table owners it should say add command. Double click it and you can enter your SQL directly in the command object…

tbrady (BOB member since 2007-09-25)

system · October 1, 2008, 9:46pm

Thanks Brady. I’d already solved it by creating a view on the table. This might come in handy one day though. Strange how one can still learn things. Because even though I’ve seen the Add command many times in the db expert it didn’t click until just now.

wahey (BOB member since 2007-05-31)