This content has been marked as final. Show 11 replies
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.
it doest work dude
"it doest work dude" is a rather broad problem description.... doesn't work how? Too many rows returned, not enough, no rows, error message, data different than expected, etc., etc. I would suspect that when you say that "....where this criteria matches ..." that it really doesn't, otherwise you would be getting the data that you expect. If you are getting nothing, the try some standard troubleshooting techniques, like removing your matching criteria, then add them back in, one at a time, until your query stops returning anything.
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...
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)
I tried that too...Please read my previous answer..
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.
genbid_assignmentinfo LEFT JOIN genbid_bidresult ON
genbid_bidresult.facsh = genbid_assignmentinfo.fasch AND
genbid_bidresult.factype = 'LT' AND
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.
Did you try SELECT DISTINCT.... ?
it gives me what I want except one extra row which exists in result table.
My updated query:
, initcap(r.lastname) lastname
, initcap(replace(r.firstname,' ','')) firstname
, initcap(r.firstname) dspfirstname
from genbid_assignmentinfo i, genbid_bidresult r
r.facsch(+) = i .facsch
and (i.factype ='LT' and r.facname(+) = i .facname)
by i .facname
, i .facsch
it is done. Thanks all.
Here is my code :
, initcap(lastname) lastname
, initcap(replace(firstname,' ','')) firstname
, initcap(firstname) dspfirstname
, initcap(genbid_bidresult.lastname) lastname
, initcap(replace(genbid_bidresult.firstname,' ','')) firstname
, initcap(genbid_bidresult.firstname) dspfirstname
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'