Results 1 to 14 of 14

Thread: Joins???

  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unhappy Unanswered: Joins???

    Hi,
    I am stuck with a problem....
    I have to query dat from two tables...
    PO_hdr and po_addl_cost
    now some po's have additional costs and if they do have there will be an entry in po_addl_cost table. They are linked via the PO_GRP_NO.

    Now I want to get an extract of data of specific fields..for all po's

    I want the extract to show
    po_no po_desc po_cost po_addl_costid po_addl_cost_value

    The first three fields are from po_hdr and the last two from po_addl_cost

    now if there are no entries for that particular po_grp_no i want the two fields blank but still want the other data.

    This is my query:
    select po.po_no,po.PO_PROJ_NM,po.LOGIN_ID,addl.PO_ADDL_CO ST_TYPE_ID,addl.PO_ADDL_COST_BUY_PRICE from po_hdr po,po_addl_cost_dtl addl
    where
    po.SITE_ID=41
    And po.PO_NO in(287,58)
    and po.STATUS_CD=5
    and addl.SITE_ID=41
    and addl.STATUS = 'A'
    and addl.PO_GRP_NO=po.PO_GRP_NO

    Pleaseeeeeeeeeeee help!!!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
    from po_hdr po
    LEFT OUTER JOIN
    po_addl_cost_dtl addl ON
    addl.PO_GRP_NO=po.PO_GRP_NO AND
    po.SITE_ID=41 AND
    po.PO_NO in(287,58) AND
    po.STATUS_CD=5 AND
    addl.SITE_ID=41 AND
    addl.STATUS = 'A';
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    6

    Question

    Thanks , but I am getting an error when trying to execute this using toad ....it gives ORA-00933 sql comman not properly ended ...highlighting "LEFT"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just a guess but perhaps your version of oracle does not support LEFT OUTER syntax

    you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on

    (sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    6

    Angry

    All sarcasm welcome...
    but I amstill having issues...
    first of all from what i remember the query with (+) goes like this

    select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
    from po_hdr po ,po_addl_cost_dtl addl where
    addl.PO_GRP_NO=po.PO_GRP_NO (+)
    AND po.SITE_ID=41
    AND po.PO_NO in(287,58) AND po.STATUS_CD=5
    AND addl.SITE_ID=41 AND addl.STATUS = 'A';

    I have absolutely no idea of joins...but this doesnt seem to retireve two rows...which is what i want.
    it gives just one row po_no of which is present in the addl_cost table.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by r937
    you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on

    (sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)
    It goes on the "outer" (dark) side:

    select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
    from po_hdr po,
    po_addl_cost_dtl addl
    where
    addl.PO_GRP_NO(+)=po.PO_GRP_NO AND
    po.SITE_ID=41 AND
    po.PO_NO in(287,58) AND
    po.STATUS_CD=5 AND
    addl.SITE_ID(+)=41 AND
    addl.STATUS (+)= 'A';

    But tell me: what is "LEFT" about an outer join? Especially when if written on one line the "outer" table appears on the right... ;o)

  7. #7
    Join Date
    Jan 2004
    Posts
    6

    Thumbs up

    That worked!!! thanks a lot!!!!!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dunno which one you'd call the outer table, but it's trivial to decide which one's the left table

    here, give it a try --

    ... FROM FOO LEFT OUTER JOIN BAR

    now, you've got FOO on the left, and BAR on the right, right?

    so, um, FOO is the left table and BAR is the right table

    gee i hope i've got that right

    i know it's probably confusing because when i write sql i never put them on the same line, i always write them on separate lines like this --

    FROM FOO
    LEFT OUTER
    JOIN BAR

    but that's because i'm an old keyboard jockey, and when i edit text, for example to replace INNER with LEFT OUTER as sometimes is necessary, then i use the arrow keys to position myself on that line, press the Home key if i'm not at the front of the line, and then while pressing the shift key, arrow down to highlight the entire line, and begin typing the replacement text

    i don't use a mouse for text editing, and consequently prefer to have stuff on multiple source lines
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Hmm, maybe I've always had it wrong about what the word "outer" really means in this context. I would have called BAR the "outer" table in your example, because in my warped mind you sort of stick the matching rows from BAR on the "outside" of the FOO records...?

    But if LEFT OUTER implies that the "outer" table is on the left (i.e FOO), then perhaps the analogy is more with program logic:

    Code:
    -- Outer query
    for foo_row in (select * from foo) loop
      -- Inner query
      begin
        select * into bar_row from bar where ...;
      exception
        when no_data_found then
          bar_row := null;
      end;
      Display(foo_row, bar_row);
    end loop;
    Presumably there is a RIGHT OUTER that does the opposite?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, RIGHT OUTER is the opposite of LEFT OUTER

    did not really understand your code, there is no looping in sql

    i would not get into the semantic morass of which one to call the outer table, since in an outer join, one of the tables brings a few extra rows to the table (if you'll pardon the pun), i.e. extra rows which aren't there in the inner join, so these extra rows would be outside the inner rows, and since in a LEFT join they come from the left table, it might make more sense to call the left table the outer table, if you know what i mean

    in any case, like i said, i don't call either of them the outer table, i just use the words left and right, because there's no ambiguity there

    sample data:

    Pets
    1 dog
    2 cat
    3 bird
    4 ferret

    People
    35 curly
    38 larry
    39 moe

    PeoplePets
    35 2
    35 3
    39 1

    list all pets, and their people if any (RIGHT join) --

    moe dog
    curly cat
    curly bird
    NULL ferret


    see this other thread for LEFT and INNER joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, my code was supposed to represent what SQL might be doing "under the covers". Or at least, the procedural code you could write to simulate an outer join.

    Yes, I agree there is nothing ambigous about LEFT and RIGHT, but then there is nothing particularly meaningful either:

    Originally posted by r937
    ... FROM FOO LEFT OUTER JOIN BAR

    now, you've got FOO on the left, and BAR on the right, right?

    so, um, FOO is the left table and BAR is the right table
    My response to that is:

    Originally posted by me
    ... FROM FOO RIGHT OUTER JOIN BAR

    now, you've got FOO on the left, and BAR on the right, right?

    so, um, FOO is the left table and BAR is the right table
    What's the difference? ;o)

    I am sure that the word OUTER must be intended to convey some meaning, but I am no longer so sure what that meaning is...

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i wrote

    ... FROM FOO LEFT OUTER JOIN BAR

    and you suggested

    ... FROM FOO RIGHT OUTER JOIN BAR

    and then asked "What's the difference?"

    well, the difference is, the first is a left outer join, and the second is a right outer join

    did my people/pets example not help?


    lemme know when you want to get into the FULL OUTER JOIN

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, and by the way, i never write RIGHT OUTER joins anyway

    i always re-write them as LEFT OUTER joins

    that's because

    ... FROM FOO RIGHT OUTER JOIN BAR

    is exactly equivalent to

    ... FROM BAR LEFT OUTER JOIN FOO



    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I have absolutely no problem understanding what LEFT, RIGHT and FULL outer joins do, I just don't quite understand why LEFT and RIGHT are so named!

Posting Permissions

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