Hi,

How can I make a left join in Informix?

and

In Oracle exists the isnull() proc, in informix how it would be?

Thanks in advantage...

Hi,

try this:
select table1.* from table1 left join table2 on table1.column1=table2.column2 where table2.column2 is null

(if you want to select only rows of table1 that didn't have a corresponding row in table2, otherwise leave out the where-clause and change your selected fields)

Hope this helps
ifx

[QUOTE=ifx]Hi,

try this:
select table1.* from table1 left join table2 on table1.column1=table2.column2

I did it but it doesn't work... My informix is the version 7 dynamic server.

For instance: select tb1.Name || ' - ' tb2.Flag
from table1 tb1 left join table2 tb2 on tb1.ID =tb2.ID

I need when tb2.Flag will be null, the select shows me only the Name, otherwise it must show me the Name plus '-' plus Flag.

How can I do it?

Thanks again....

Hi,

Try this:

select tb1.Name , ' - ' ,tb2.Flag
from table1 tb1 outer table2 tb2
where tb1.ID =tb2.ID

If you need to further alter your select, you can use select CASE expression on the condition of tb2.flag being null.

Bye

Nitin

Sorry, I suppose, outer works with a comma between table names:

select tb1.Name , ' - ' ,tb2.Flag
from table1 tb1, outer table2 tb2
where tb1.ID =tb2.ID

Bye

Nitin

Hi efrenba,

I hope this is what you need:

select case when tb2.ID is null then tb1.Name else tb1.Name || ' - ' || tb2.Flag end
from table1 tb1 left join table2 tb2 on tb1.ID = tb2.ID

This should be the same:

select case when tb2.ID is null then tb1.Name else tb1.Name || ' - ' || tb2.Flag end
from table1 tb1, outer table2 tb2 where tb1.ID = tb2.ID

Best regards
ifx

Hi efrenba,

I hope this is what you need:

select case when tb2.ID is null then tb1.Name else tb1.Name || ' - ' || tb2.Flag end
from table1 tb1 left join table2 tb2 on tb1.ID = tb2.ID

This should be the same:

select case when tb2.ID is null then tb1.Name else tb1.Name || ' - ' || tb2.Flag end
from table1 tb1, outer table2 tb2 where tb1.ID = tb2.ID

Best regards
ifx


Thanks, I solved the problem.............