• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Join 2 tables

Participant ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

I am trying to get data from genbid_assignmentinfo and genbid_bidresult where this criteria matches ...The facname,facsch, factype must match in both table along with this genbid_bidresult.isassigned = 'Y' and genbid_bidresult.istemp = 'N' ..I need you help ...
TOPICS
Advanced techniques

Views

827

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

it doest work dude

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

"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.

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

Did you try SELECT DISTINCT.... ?

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 09, 2007 Jul 09, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 10, 2007 Jul 10, 2007

Copy link to clipboard

Copied

LATEST
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'

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation