13 Replies Latest reply on Apr 23, 2019 4:02 PM by Frank Kulash

    Get data from tables using Join condition

    Xandot

      Hello,

       

      I would like to fetch the data from table1 based on the records that are present in table2.

       

      Table1 & Table2 Scripts:

      with table1 as
      (select 'd001' col1, 629 col2, null col3, 101 col4 from dual
      union 
      select 'd001' col1, 649 col2, null col3, 101 col4 from dual
      union 
      select 'd001' col1, 650 col2, null col3, 101 col4 from dual
      union 
      select 'd001' col1, 610 col2, null col3, 101 col4 from dual
      union 
      select 'd002' col1, 31 col2, 8 col3, 102 col4 from dual
      union 
      select 'd002' col1, 31 col2, 10 col3, 102 col4 from dual
      union 
      select 'd002' col1, 31 col2, 195 col3, 102 col4 from dual
      union 
      select 'd002' col1, 31 col2, 193 col3, 102 col4 from dual) select * from table1;
      
      
      with table2 as
      (select 'd001' col1, 629 col2, null col3, 101 col4 from dual
      union 
      select 'd001' col1, 649 col2, null col3, 101 col4 from dual
      union 
      select 'd001' col1, 650 col2, null col3, 101 col4 from dual
      union 
      select 'd002' col1, null col2, 8 col3, 102 col4 from dual
      union 
      select 'd002' col1, null col2, 10 col3, 102 col4 from dual
      union 
      select 'd002' col1, null col2, 195 col3, 102 col4 from dual) select * from table2;
      

       

      The output should look like:

       

      Map info: if a table1 record matched with table2 then just information about whather its present in table2 or not.

       

      col1col2col3col4map info
      d001629 null101In table2
      d001649null 101In table2
      d001650null 101In table2
      d002null 8102In table2
      d002null 10102In table2
      d002null 195102In table2
      d001610null101Not in Table2
      d00231193102Not in Table2

       

      Please help me out to get this output.

       

      Thanks in advance

        • 1. Re: Get data from tables using Join condition
          Solomon Yakobson

          Can same row appear in table1 and/or table2 more than once?

           

          SY.

          • 2. Re: Get data from tables using Join condition
            mathguy

            The values you show in the "desired output" are not the values in TABLE1 you have in the WITH clause. Why is that?

             

            Then:  what does it mean for records to "match"? That is not a technical term. I can only guess that you want the "records" (in SQL they are called "rows") to be considered a "match" if for each column, they either have the same non-NULL value or they are both NULL (meaning, the column value is NULL in both tables).

             

            If so (and even if not - if you define "match" in some other way), what you need is a semi-join, since you only want to see if there's a "match" - you don't need to know how many matches, and you are not retrieving any other data from TABLE2. Something like this will suffice. Notice the use of DECODE, under which NULL is "the same as" NULL; if you just use equality conditions, NULL will cause problems.

             

            select t1.*,

                   case when exists ( select null

                                      from   table2

                                      where  decode(col1, t1.col1, 0) is not null

                                        and  decode(col2, t1.col2, 0) is not null

                                        and  decode(col3, t1.col3, 0) is not null

                                        and  decode(col4, t1.col4, 0) is not null

                                    )

                        then 'In table2' else 'Not in table2' end as map_info

            from   table1 t1

            ;

            1 person found this helpful
            • 3. Re: Get data from tables using Join condition
              jaramill

              First, always put your Oracle database version.  This is from the FAQ (Frequently Asked Questions) link on --> Re: 2. How do I ask a question on the forums?

               

              5) Database Version and IDE Version
              Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

               

              If you're not sure what it is you can do the following:

              select * from v$version;

              in an SQL*Plus session and paste the results.

              Second, what query have YOU written/tried that is giving you problems??  Show us that you tried and need help, and not just give us data for us to do your work or homework for you.

              • 4. Re: Get data from tables using Join condition
                Frank Kulash

                Hi,

                 

                Xandot wrote:

                 

                Hello,

                 

                I would like to fetch the data from table1 based on the records that are present in table2.

                 

                Table1 & Table2 Scripts:

                1. withtable1as
                2. (select'd001'col1,629col2,nullcol3,101col4fromdual
                3. union
                4. select'd001'col1,649col2,nullcol3,101col4fromdual
                5. union
                6. select'd001'col1,650col2,nullcol3,101col4fromdual
                7. union
                8. select'd001'col1,610col2,nullcol3,101col4fromdual
                9. union
                10. select'd002'col1,31col2,8col3,102col4fromdual
                11. union
                12. select'd002'col1,31col2,10col3,102col4fromdual
                13. union
                14. select'd002'col1,31col2,195col3,102col4fromdual
                15. union
                16. select'd002'col1,31col2,193col3,102col4fromdual)select*fromtable1;
                17. withtable2as
                18. (select'd001'col1,629col2,nullcol3,101col4fromdual
                19. union
                20. select'd001'col1,649col2,nullcol3,101col4fromdual
                21. union
                22. select'd001'col1,650col2,nullcol3,101col4fromdual
                23. union
                24. select'd002'col1,nullcol2,8col3,102col4fromdual
                25. union
                26. select'd002'col1,nullcol2,10col3,102col4fromdual
                27. union
                28. select'd002'col1,nullcol2,195col3,102col4fromdual)select*fromtable2;

                 

                The output should look like:

                 

                Map info: if a table1 record matched with table2 then just information about whather its present in table2 or not.

                 

                col1
                col2
                col3
                col4
                map info
                d001629null101In table2
                d001649null101In table2
                d001650null101In table2
                d002null8102In table2
                d002null10102In table2
                d002null195102In table2
                d001610null101Not in Table2
                d00231193102Not in Table2

                 

                Please help me out to get this output.

                 

                Thanks in advance

                Thanks for posting the sample data and results.  Don't forget to post an explanation of how you get those results from that data.

                What does each row in the result set represent?

                Will there always be exactly as many rows in the result set as there are in table1?

                What does "match" mean in this problem?

                 

                Always say which version of Oracle you're using (for example, 12.2.0.1.0).

                See the Forum FAQ: Re: 2. How do I ask a question on the forums?

                • 5. Re: Get data from tables using Join condition
                  Xandot

                  Hello,

                   

                  Acknowledged, Apologies for the inconvenience. In the future, I will post with more descriptive manner.

                  • 6. Re: Get data from tables using Join condition
                    Xandot

                    DB Version: Oracle 12 c

                    • 7. Re: Get data from tables using Join condition
                      Cookiemonster76

                      12c covers lots of versions. We need the specific version, for example (as already mentioned) 12.2.0.1.0

                      You can get it by querying v$version.

                      • 8. Re: Get data from tables using Join condition
                        Frank Kulash

                        Hi,

                        Xandot wrote:

                         

                        DB Version: Oracle 12 c

                        There is no Oracle version 12 b or 12 d, so it's pretty silly to say you're using 12 c.  Why not give your exact version (e.g. 12.2.0.2.0), as shown in reply #4?

                         

                        Don't forget to explain how you get the desired results from the given data.

                        • 9. Re: Get data from tables using Join condition
                          Xandot

                          Basically, I want to fetch rows from table1 and if col1 or col2 or col3 or col4 matching row exists in table2 then map info column should be update as "In table2 / No in Table2".

                           

                          following rules to the featch table from table1:

                          If col3 matched from table2 col3 then skip the other checks

                          If col3 is not matched then check col2 match with table2 col2 and skip the other checks

                          If col2 is not matched then check col1 match with table2 col1 and skip the other checks

                          • 10. Re: Get data from tables using Join condition
                            Xandot

                            using Oracle 12.2.0.1 - Oracle Database 12c Release 2

                            • 11. Re: Get data from tables using Join condition
                              Xandot

                              Yes, description and data are not sufficient,

                              I will post again this problem with all the required data and description.

                               

                              Thanks

                              • 12. Re: Get data from tables using Join condition
                                mathguy

                                Xandot wrote:

                                 

                                Basically, I want to fetch rows from table1 and if col1 or col2 or col3 or col4 matching row exists in table2 then map info column should be update as "In table2 / No in Table2".

                                 

                                following rules to the featch table from table1:

                                If col3 matched from table2 col3 then skip the other checks

                                If col3 is not matched then check col2 match with table2 col2 and skip the other checks

                                If col2 is not matched then check col1 match with table2 col1 and skip the other checks

                                 

                                 

                                OK, then one change to the solution in Reply 2 is to use OR instead of AND in the condition in the WHERE clause.

                                 

                                You still didn't explain what "matched" means. In particular, now that you explained that a "match" - whatever that means - in any column is sufficient, the important remaining question is about NULL. Does a NULL in table1.col2 match a NULL in table2.col2?

                                 

                                Even with the most restrictive condition - where NULL doesn't match NULL - it is still the case that in your sample INPUTS, every row from table1 has at least one match in table2. So how do you get ANY rows with 'Not in table2' in your output?

                                • 13. Re: Get data from tables using Join condition
                                  Frank Kulash

                                  Hi,

                                  Xandot wrote:

                                   

                                  Basically, I want to fetch rows from table1 and if col1 or col2 or col3 or col4 matching row exists in table2 then map info column should be update as "In table2 / No in Table2".

                                   

                                  following rules to the featch table from table1:

                                  If col3 matched from table2 col3 then skip the other checks

                                  If col3 is not matched then check col2 match with table2 col2 and skip the other checks

                                  If col2 is not matched then check col1 match with table2 col1 and skip the other checks

                                  ...

                                  According to those rules, for the sample data you posted, wouldn't all the rows in table1 match at least 1 row in table2?

                                   

                                  For example, this row from table1:

                                  select 'd001' col1, 610 col2, null col3, 101 col4 from dual

                                  First we check if it matches any col3 from table2; but it doesn't.  (I'm just guessing here; you still haven't explained exactly what "match" means.)

                                  So next we check if it matches any col2 from tabl2; but it doesn't.

                                  So next we check if matches any col1; and it does.  In fact, there are 3 rows in table2 that have the same col1 value, 'd001'.

                                  Why do you say that row in table1 does not match any row in table2?