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!!!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';|||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"|||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?)|||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.|||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)|||That worked!!! thanks a lot!!!!!|||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 :cool:

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

-- 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?|||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 (http://www.dbforums.com/showthread.php?threadid=976339&postid=3597190#post3597190) for LEFT and INNER joins|||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...|||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

:cool:|||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?|||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!