ODBC outer join question

Hi, I have a question on ODBC outer joins. Look at the next situation: Table A column Id column B_Id ref to table B column C_Id ref to table C Table B column Id column Name Table C column Id column Name I Would like to get all records from table A, including the name columns from table B and C, in ODBC, on a SQL Server and a Informix database. According the odbc syntax the query should be something like: SELECT A.Id, B.Name, C.Name FROM { oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON A.B_Id = B.Id ON A.C_Id = C.Id } This doesn't work for Informix. Could somebody help me?????? Thank's -- Perry van Kuppeveld WinIt Software Engineering Adres: Arnoud van Gelderweg 5 5361 CT GRAVE Tel: 0486 42 29 29 Fax: 0486 42 27 02 Email: perry@telebyte.nl

We once had this problem with an old version of Crystal Reports. Upgrading the version fixed it. -- Bashar Chalabi CTL, London Perry van Kuppeveld <perryNOSPAM@winit.nl> wrote in message news:82qjju$mm1$1@reader2.wxs.nl... > Hi, > > I have a question on ODBC outer joins. Look at the next situation: > > Table A > column Id > column B_Id ref to table B > column C_Id ref to table C > > Table B > column Id > column Name > > Table C > column Id > column Name > > I Would like to get all records from table A, including the name columns > from table B and C, in ODBC, on a SQL Server and a Informix database. > According the odbc syntax the query should be something like: > > SELECT A.Id, B.Name, C.Name FROM { oj A LEFT OUTER JOIN B LEFT OUTER JOINC > ON A.B_Id = B.Id ON A.C_Id = C.Id } > > This doesn't work for Informix. Could somebody help me?????? > > Thank's > > -- > Perry van Kuppeveld > WinIt Software Engineering > > Adres: Arnoud van Gelderweg 5 > 5361 CT GRAVE > Tel: 0486 42 29 29 > Fax: 0486 42 27 02 > Email: perry@telebyte.nl > > >

Hi Perry, is it right that this is an generated select by Crystal Reports ? Dirk Perry van Kuppeveld schrieb: > Hi, > > I have a question on ODBC outer joins. Look at the next situation: > > Table A > column Id > column B_Id ref to table B > column C_Id ref to table C > > Table B > column Id > column Name > > Table C > column Id > column Name > > I Would like to get all records from table A, including the name columns > from table B and C, in ODBC, on a SQL Server and a Informix database. > According the odbc syntax the query should be something like: > > SELECT A.Id, B.Name, C.Name FROM { oj A LEFT OUTER JOIN B LEFT OUTER JOIN C > ON A.B_Id = B.Id ON A.C_Id = C.Id }> > This doesn't work for Informix. Could somebody help me?????? > > Thank's > > -- > Perry van Kuppeveld > WinIt Software Engineering > > Adres: Arnoud van Gelderweg 5 > 5361 CT GRAVE > Tel: 0486 42 29 29 > Fax: 0486 42 27 02 > Email: perry@telebyte.nl

Please correct your eMail-adress if you'll get correct answers. Perry van Kuppeveld schrieb: > Hi, > > I have a question on ODBC outer joins. Look at the next situation: > > Table A > column Id > column B_Id ref to table B > column C_Id ref to table C > > Table B > column Id > column Name > > Table C > column Id > column Name > > I Would like to get all records from table A, including the name columns > from table B and C, in ODBC, on a SQL Server and a Informix database. > According the odbc syntax the query should be something like: > > SELECT A.Id, B.Name, C.Name FROM { oj A LEFT OUTER JOIN B LEFT OUTER JOIN C > ON A.B_Id = B.Id ON A.C_Id = C.Id }> > This doesn't work for Informix. Could somebody help me?????? > > Thank's > > -- > Perry van Kuppeveld > WinIt Software Engineering > > Adres: Arnoud van Gelderweg 5 > 5361 CT GRAVE > Tel: 0486 42 29 29 > Fax: 0486 42 27 02 > Email: perry@telebyte.nl

The problem is the ODBC syntax to use. In SQL Server the next statement gives the right result: select a.*, b.*, c.* from a,b,c where a.b_id *= b.id and a.c_id *= c.id In Informix the next statement gives the right result: select a.*, b.*, c.* from a, outer (b), outer(c) where a.b_id = b.id and a.c_id = c.id What should be the odbc version of this query, returning the right result. Perry van Kuppeveld perry@winit.nl

The syntax in your original msg looked OK, at first sight, except... maybe you should try "{oj ..." instead of "{ oj ..". Have a look at SQLNativeSQL() in the ODBC API to see whether your driver correctly translates what you're trying to do. This may also depend on the driver you use. The SQLServer driver I have here didn't have a problem with your additional blank, but Informix' may... Some drivers (especially [very?] old) ones don't support translation at all, Jorik. Perry van Kuppeveld wrote in message <82qu70$rra$1@reader3.wxs.nl>... >The problem is the ODBC syntax to use. > >In SQL Server the next statement gives the right result: > >select a.*, b.*, c.* >from a,b,c >where a.b_id *= b.id >and a.c_id *= c.id > >In Informix the next statement gives the right result: > >select a.*, b.*, c.* >from a, outer (b), outer(c) >where a.b_id = b.id >and a.c_id = c.id >