11 Replies Latest reply on Jul 10, 2007 11:03 AM by emmim44

    Join 2 tables

    emmim44 Level 1
      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 ...
        • 1. Re: Join 2 tables
          amers Level 1
          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.
          • 2. Re: Join 2 tables
            emmim44 Level 1
            it doest work dude
            • 3. Join 2 tables
              paross1 Level 2
              "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.

              • 4. Re: Join 2 tables
                emmim44 Level 1
                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...
                • 5. Re: Join 2 tables
                  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)
                  • 6. Re: Join 2 tables
                    emmim44 Level 1
                    I tried that too...Please read my previous answer..
                    • 7. Re: Join 2 tables
                      Level 7
                      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 ...
                      genbid_assignmentinfo LEFT JOIN genbid_bidresult ON
                      genbid_bidresult.facsh = genbid_assignmentinfo.fasch AND
                      genbid_bidresult.factype = 'LT' AND

                      • 8. Re: Join 2 tables
                        amers Level 1
                        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.
                        • 9. Re: Join 2 tables
                          paross1 Level 2
                          Did you try SELECT DISTINCT.... ?

                          • 10. Re: Join 2 tables
                            emmim44 Level 1
                            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

                            r.facsch(+) = i .facsch
                            and (i.factype ='LT' and r.facname(+) = i .facname)
                            by i .facname
                            , i .facsch
                            , r.lastname
                            , r.firstname
                            • 11. Re: Join 2 tables
                              emmim44 Level 1
                              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
                              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'