Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Left outer join between 2 queries

    Hello

    Could someone kindly help with a Left outer join problem I am struggling with in Informix

    I have obvisouly simplified the query where I want a left outer join between query a and query b

    select
    a.name,
    a.date
    from
    table1)a,
    (select
    b.date,
    b.name
    from
    table1)b
    where a.name=b.name(+)

    Could someone kindly help me get the correct syntax

    Thanks
    Helen

  2. #2
    Join Date
    May 2012
    Posts
    19
    select table1.name, table1.date, table2.name, table2.date from table1
    left outer join table2
    on table1.name = table2.name

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Hello,

    Thank you for this, but could you kindly show me the right syntax to write as I have to make both tables in the SQL, the tables are not already made for me and I can not just link to them I.e

    select
    a.name,
    a.date
    from
    table1)a

    and

    (select
    b.date,
    b.name
    from
    table1)b


    Any help would be greatly appreciated

    Thanks
    Helen

  4. #4
    Join Date
    May 2012
    Posts
    19
    not sure what you wish...


    create table table1 (name char(50), date datetime year to fraction);
    create table table2 (name char(50), date datetime year to fraction);

    insert into table1 (name, date) values ('a1', '2012-11-30 00:00:00.0');
    insert into table1 (name, date) values ('a2', '2012-12-30 00:00:00.0');

    insert into table2 (name, date) values ('a1', '2012-11-29 00:00:00.0');
    insert into table2 (name, date) values ('b1', '2012-11-30 00:00:00.0');
    insert into table2 (name, date) values ('b2', '2012-12-30 00:00:00.0');
    insert into table2 (name, date) values ('b3', '2012-12-30 00:00:00.0');

    select table1.name, table1.date, table2.name, table2.date from table1 left outer join table2 on table1.name = table2.name
    select table1.name, table1.date, table2.name, table2.date from table1 inner join table2 on table1.name = table2.name

  5. #5
    Join Date
    May 2012
    Posts
    19
    or is this what you want:


    select * from table1
    union
    select * from table2

  6. #6
    Join Date
    Dec 2011
    Posts
    82
    Hello,

    Thank you, but unfortunately I do not have the user rights to create and insert into tables at the workplace

    Is there any alternatives?

    Thanks
    Helen

  7. #7
    Join Date
    May 2012
    Posts
    19
    sorry, i have no clue what your question is...

  8. #8
    Join Date
    Dec 2011
    Posts
    82
    Hello,

    I want to be able to do a left outer join between Table a and Table b where a.name=b.name

    Unfortunatley I am unable to create and insert into tables

    Thanks
    helen

  9. #9
    Join Date
    May 2012
    Posts
    19
    my first post gives you the answer.

  10. #10
    Join Date
    Dec 2011
    Posts
    82
    Thank you!

    I do now see what you meant.....It works and I was being a little dense!

    Thanks for your patience

Posting Permissions

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