Results 1 to 7 of 7

Thread: Left join how?

  1. #1
    Join Date
    Jan 2006
    Posts
    8

    Unanswered: Left join how?

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

  2. #2
    Join Date
    Feb 2005
    Posts
    33

    Left Join

    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

  3. #3
    Join Date
    Jan 2006
    Posts
    8

    left join, doesn't work, help....

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

  4. #4
    Join Date
    Nov 2004
    Posts
    143
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    143
    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

  6. #6
    Join Date
    Feb 2005
    Posts
    33

    Left Join

    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

  7. #7
    Join Date
    Jan 2006
    Posts
    8

    Thumbs up

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •