Join 2 tables

it is done. Thanks all.
Here is my code :
--------------
select facname
, facsch
, initcap(lastname) lastname
, initcap(replace(firstname,' ','')) firstname
, initcap(firstname) dspfirstname
, homefac
, facspot
, facid
, factype
from (

select genbid_assignmentinfo.facname
, genbid_assignmentinfo.facsch
, initcap(genbid_bidresult.lastname) lastname
, initcap(replace(genbid_bidresult.firstname,' ','')) firstname
, initcap(genbid_bidresult.firstname) dspfirstname
, genbid_bidresult.homefac
, genbid_assignmentinfo.facspot
, genbid_assignmentinfo.facid
, genbid_assignmentinfo.factype
from genbid_assignmentinfo
left
join genbid_bidresult
on genbid_bidresult.facsch = genbid_assignmentinfo.facsch
and genbid_bidresult.facname = genbid_assignmentinfo.facname

and genbid_assignmentinfo.factype='SGT' and genbid_bidresult.factype='SGT'
and extract( year from genbid_bidresult.facdate) = extract( year from sysdate)
and genbid_bidresult.isassigned = 'Y' and genbid_bidresult.istemp = 'N'
)

where factype='SGT'

it gives me what I want except one extra row which exists in result table.

My updated query:

select i.facname
, i.facsch
, initcap(r.lastname) lastname
, initcap(replace(r.firstname,' ','')) firstname
, initcap(r.firstname) dspfirstname
, r.homefac
, i.facspot
, i.facid

from genbid_assignmentinfo i, genbid_bidresult r

where
r.facsch(+) = i .facsch
and (i.factype ='LT' and r.facname(+) = i .facname)
order
by i .facname
, i .facsch
, r.lastname
, r.firstname

Ian is correct about the syntax. Also, LEFT OUTER JOINs pick up rows that don't have the exact match. If you want only the matches that are in BOTH tables.. then you need to use INNER JOINs. If it doesn't matter that it's not in one table, then OUTER JOINs will suffice.

Your mixing up your JOIN syntax and your WHERE syntax. All your join ON
clauses should be aField.aTable = aField.bTable nothing set to a
specific value. Those clauses should be in a where block.

SELECT ...
FROM
genbid_assignmentinfo LEFT JOIN genbid_bidresult ON
genbid_bidresult.facsh = genbid_assignmentinfo.fasch AND
...
WHERE
genbid_bidresult.factype = 'LT' AND
...

I tried that too...Please read my previous answer..

select * from genbid_assignmentinfo g_a, genbid_bidresult g_b
where g_a.facname = g_b.facname
and g_a.facsch = g_b.facsch
and g_a.factype = g_b.factype
and g_b.isassigned = 'Y'
and g_b.istemp = 'N'

of course, replace the * with whatever you want... (I just didn't feel like retyping all you typed)

when I use outher join, it returns lots of row along with repeating the firstname from result table for all rows..Example: I have 2 test rows in result table which have match in info table..it is supposed return 13 records two of them should contains firtsname and lastname...

it doest work dude

Why not do an INNER JOIN, instead of an OUTER JOIN. This will make sure that the data is in both tables for the records to return.