Results 1 to 7 of 7

Thread: Join Statment

  1. #1
    Join Date
    Oct 2002
    Posts
    9

    Question Unanswered: Join Statment

    I have been working with a join statement but can't get it right. This is what I am trying to do.


    This is what I have:
    Table_1
    Param Value
    1 A
    3 B
    5 C

    Table_2
    Param
    1
    2
    3
    4
    5
    6

    This is what I want:
    Param Value
    1 A
    2
    3 B
    4
    5 C
    6

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think you need

    select table2.param,
    table1.value
    from table2,
    table1
    where table1.param (+) = table2.param

    HTH
    Bill

  3. #3
    Join Date
    May 2003
    Location
    pakistan
    Posts
    9
    the query which i have executed goes like this
    select table_1.param, table_1.value , table_2.param
    from table_1, table_2
    where table_1.param=table_2.param
    ;
    hope this one works

  4. #4
    Join Date
    Oct 2002
    Posts
    9
    Originally posted by billm
    Hi,

    I think you need

    select table2.param,
    table1.value
    from table2,
    table1
    where table1.param (+) = table2.param

    HTH
    Bill
    This returns only the params from table 1. I want the params that exist in both tables to be returned but I do not want the params to be returned more then one time.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select table_2.param, table_1.value
    from table_2
    left outer join table_1
    on table_2.param = table_1.param

    rudy

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Rich,

    Rudy's reply (I think) is a SQL or Oracle syntax of mine - I'm not sure, but it returns the same result. I work only on Oracle systems so I don't pay much attention to what is SQL92/97 syntax, only Oracle syntax. I think his answer is a syntactically different, but ultimately the same answer.

    Regardless of that, I've just ran a create table script for table1 and table2 and my original select statement and it gives exactly the result you want. Either you didn't copy and paste, or you typed the select and missed the (+) or something I don't know. Failing that you've not clearly defined what you want?

    This works, I have just ran it here.....

    create table table1 (param number(1), value varchar2(1) );
    create table table2 (param number(1));
    insert into table1 (param,value) values (1,'A');
    insert into table1 (param,value) values (3,'B');
    insert into table1 (param,value) values (5,'C');
    insert into table2 (param) values (1);
    insert into table2 (param) values (2);
    insert into table2 (param) values (3);
    insert into table2 (param) values (4);
    insert into table2 (param) values (5);
    insert into table2 (param) values (6);

    If it's still not working then there is some voodoo involved.

    select table2.param,
    table1.value
    from table2,
    table1
    where table1.param (+) = table2.param;

    If that still doesn't work I'll spool the output.

    HTH
    Bill

  7. #7
    Join Date
    Oct 2002
    Posts
    9

    Red face

    That worked! I must have typed it incorrectly (it was Friday and my brain was fried). Thanks for your help!

    Rich

Posting Permissions

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