3 Replies Latest reply: Sep 20, 2006 10:22 AM by 449096 RSS

    oracle left join

    73941
      e.g. i have 2 table

      table_a

      subject
      math
      phy
      chem
      bio

      table_b
      subject member

      math peter
      phy peter
      phy john
      bio tim

      1) i create a view as follow:

      create or replace view view_test
      (subject,
      member)
      as select a.subject,
      b.member
      from table_a a
      left outer join table_b b
      on (a.subject = b.subject)

      how i can get the result from view when the sql is

      select * from view_test where member = 'tim';

      subject member
      ------- ------
      math
      phy
      chem
      bio tim

      i can do it in sql statment as follow;

      select a.subject,
      b.member
      from table_a a
      left outer join table_b b
      on (a.subject = b.subject and
      b.member = 'tim')
        • 1. Re: oracle left join
          John Spencer
          What part of it doesn't work for you?
          SQL> SELECT * FROM tablea;

          SUBJECT
          ----------
          math
          phy
          chem
          bio

          SQL> SELECT * FROM tableb;

          SUBJECT    MEMBER
          ---------- ----------
          math       peter
          phy        peter
          phy        john
          bio        tim

          SQL> CREATE VIEW view_test AS
            2  SELECT a.subject, b.member
            3  FROM tablea a
            4       LEFT OUTER JOIN tableb b
            5       ON (a.subject = b.subject);
           
          View created.

          SQL> SELECT *
            2  FROM view_test
            3  WHERE member = 'tim';

          SUBJECT    MEMBER
          ---------- ----------
          bio        tim
          John
          • 2. Re: oracle left join
            449096
            I see what's being asked for here - and why it won't work with the view.

            You want to see all SUBJECTs and also match with the MEMBER name you're looking for against the subjects which appear in table B.

            Your view is giving you the result of the outer join - by using "where member='tim'" you are seeing just the one row which has tim, i.e. "bio tim", because the other rows don't match (they eitehr have a member name in or is NULL)

            This works...

            select distinct(subject), decode(member,'TIM',member,null)
            from view_test
            /

            you could perform a decode to get what you want but you might just as well use the SQL statement you refer to in your original posting.

            HTH
            Dave
            • 3. Re: oracle left join
              449096
              at least it does work with the sample you gave...add a few more rows and even the distinct/decode needs some work!

              Go with your straight SQL, so much easier!

              D. ;o)