This discussion is archived
14 Replies Latest reply: Nov 26, 2013 7:23 AM by 799664 RSS

SQL Join between two tables two columns, but the data in the join condition could be null.

799664 Newbie
Currently Being Moderated

hello all,

 

can anyone help me write an outer join b/n the two tables below. The joining condition has if's and or's.

 

table 1 has 2 million rows, table 2 is very small

 

TABLE1
CUSTOMER_IDCITYSTATE
1SKOKIEIL
2CHICAGOIL
3CARYNC
.ERIEPA
.PHILLYPA
CHARLOTENC
.
.
.
2 MILLIONCITYXYSTATEX

 

TABLE2
CITYSTATECONTACT
ILOJO
ERIE BRITT
PAMIKE
PITTSBURGPAHILTON
N043TATaffi
Baffi
Rb0b
Qb0b
CHARLOTENCb0b

 

problem :: for all the data in table1, I need to find out the CONTACT from table 2 And the join condition would be as below

1. either both TABLE1.CITY=TABLE2.CITY AND TABLE1.STATE=TABLE2.STATE and get CONTACT
OR
2. TABLE1.CITY=TABLE2.CITY AND TABLE2.STATE IS NULL  and get the value of CONTACT
OR
3. TABLE1.STATE=TABLE2.STATE AND TABLE2.CITY is null and get the value of CONTACT

 

I need a query like this

SELECT A.CUSTOMER_ID, A.CITY, A.STATE, B.CONTACT
FROM TABLE1 A, TABLE2 B
WHERE (join condition fitting in the 3 condition mentioned above)
  • 1. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    From what you've said so far, the join condition you want is:

     

    (

            table2.city   = table1.city

    AND  (  table2.state  = table1.state

         OR table2.state  IS NULL

         )

    )

    OR

    (    (  table2.city   = table1.city

         OR table2.city   IS NULL

         )

    AND     table2.state  = table1.state

    )

    If the relevant columns in table1 are never NULL, or if you know of some value (such as as '??' that they never equal) then you can simplify the conditions above.

     

    I hope this answers your question.
    If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 2. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    799664 Newbie
    Currently Being Moderated

    I would say we are thinking in the same direction, but this needs to be an outer join.. yielding all from table1 and matching (based on condition we are working on) from table2

     

    I would tweak your join condition like below, but that throws an outer join error.

     

    FROM TABLE1 A, TABLE2 B  

    WHERE A.CUSTOMER_ID =5

    AND (

            (

                    A.CITY=B.CITY(+)

                AND A.STATE=B.STATE(+)

            )

            OR

            (

                    A.CITY=B.CITY(+)

                AND A.STATE IS NULL      

            )

            OR

            (

                    A.STATE=B.STATE(+)

                AND A.CITY IS NULL       

            )

        )

     

    But, this throws an error, ORA-01719: outer join operator  not allowed in operand of OR or IN

  • 3. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    CGomes Newbie
    Currently Being Moderated

    Hi,

     

    Can you try the following code:

     

    SELECT t1.customer_id, t1.city, t1.state, to_char(wm_concat(t2.contact)) contacts

    FROM table1 t1, table2 t2

    WHERE REGEXP_LIKE(t1.city||';'||t1.state, '^'||NVL(t2.city,'([A-Z])*')||';'||NVL(t2.state,'([A-Z])*')||'$','i')

    GROUP BY t1.customer_id, t1.city, t1.state;

     

    According the values it seems you can get more than one contact for a customer.

    Hope it helps.

  • 4. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    I recommend using ANSI join notation for all joins, especially outer joins.  For example:

     

    SELECT  t1.customer_id, t1.city, t1.state, t2..contact

    FROM             table1  t1

    LEFT OUTER JOIN  table2  t2  ON  (       t2.city   = t1.city

                                     AND  (  t2.state  = t1.state

                                          OR t2.state  IS NULL

                                          )

                                     )

                                 OR

                                     (    (  t2.city   = t1.city

                                          OR t2.city   IS NULL

                                          )

                                     AND     t2.state  = t1.state

                                     )

    WHERE   t1.customer_id  = 5

    ;

    ORA-01719 only occurs when using the old join notation.  This is just one of the reasons why you should use ANSI join syntax.

  • 5. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    799664 Newbie
    Currently Being Moderated

    Thanks for helping.. wm_concat is throwing an invalid identified error.  I didnt understand how did you join in regexp_like.. ( is that an outer join ). Yes multiple values may return from, flexibility is we can consider any value.

     

    we are on

    BANNER

     

     

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    PL/SQL Release 11.2.0.2.0 - Production

    CORE 11.2.0.2.0 Production

    TNS for Solaris: Version 11.2.0.2.0 - Production

    NLSRTL Version 11.2.0.2.0 - Production

  • 6. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    CGomes Newbie
    Currently Being Moderated

    You can just remove the wm_concat function and the "group by" and see what happens

     

    SELECT t1.customer_id, t1.city, t1.state, t2.contact

    FROM table1 t1, table2 t2

    WHERE REGEXP_LIKE(t1.city||';'||t1.state, '^'||NVL(t2.city,'([A-Z])*')||';'||NVL(t2.state,'([A-Z])*')||'$','i')

    ;

  • 7. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    799664 Newbie
    Currently Being Moderated

    Your approach is interesting, but yielding wrong results. I will try to debug REGEXP that you suggested, If not then I will post the table structure and insert statements here.

  • 8. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    CGomes Newbie
    Currently Being Moderated

    I think that using the regexp should be faster than the other solution, but correct me if im wrong. You may need adjust the regular expression to get the expected results.

  • 9. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    799664 Newbie
    Currently Being Moderated

    If I tweek the REGEXP like below then the query is returning duplicates, if I use a group by then the count is correct. Is there is a way to avoid duplicates returned by REGEXP ?

     

    WHERE REGEXP_LIKE(t1.city||';'||t1.state, '^'||NVL(t2.city,t1.city)||';'||NVL(t2.state,t1.state)||'$','i')



    mean while I will check the sanity of the result set.

  • 10. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    CGomes Newbie
    Currently Being Moderated

    Hi. I know this tow ways to group the results.

    I made a regular expression based on the entries in your post.

     

    SELECT t1.customer_id, t1.city, t1.state, append(t2.contact) contacts

    FROM table1 t1, table2 t2

    WHERE REGEXP_LIKE(t1.city||';'||t1.state, '^'||NVL(t2.city,'([0-9a-zA-Z])*')||';'||NVL(t2.state,'([0-9a-zA-Z])*')||'$','i')

    GROUP BY t1.customer_id, t1.city, t1.state

    ;

     

     

    SELECT t1.customer_id, t1.city, t1.state, listagg(t2.contact,',') within group (order by t1.customer_id, t1.city, t1.state) contacts

    FROM table1 t1, table2 t2

    WHERE REGEXP_LIKE(t1.city||';'||t1.state, '^'||NVL(t2.city,'([0-9a-zA-Z])*')||';'||NVL(t2.state,'([0-9a-zA-Z])*')||'$','i')

    GROUP BY t1.customer_id, t1.city, t1.state

    ;

     

    Hope it helps.

  • 11. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    799664 Newbie
    Currently Being Moderated

    All this is helpful my dear ...

     

    except the in 1 of the following scenario. with Table1 has a city, state which no entries in Table2 city,sate. This scenario is getting filtered out. Basically I need all from Table1 even if nothing matches in Table2. This points back to my first question.... how are you defining an outer join ??    

  • 12. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    Sudhakar_B Pro
    Currently Being Moderated

    Dear OP,

     

    Do you want something like this?

     

    > with t1 as

    -- Start of SAMPLE DATA

    (select 1 CUSTOMER_ID, 'SKOKIE' CITY, 'IL' STATE from dual union

    select 2, 'CHICAGO', 'IL'  from dual union

    select 3, 'CARY', 'NC' from dual union

    select 4, 'ERIE', 'PA'  from dual union

    select 5, 'PHILLY', 'PA'  from dual union

    select 6, 'CHARLOTE', 'NC' from dual)

    ,

    t2 as

    (select null CITY, 'IL' STATE, 'OJO' CONTACT from dual union

    select  'ERIE', null, 'BRITT'  from dual union

    select null, 'PA', 'MIKE'  from dual union

    select 'PITTSBURG', 'PA', 'HILTON'  from dual union

    select 'N043', 'TAT', 'affi'  from dual union

    select null,'B', 'affi'  from dual union

    select null,'R', 'b0b'  from dual union

    select null,'Q', 'b0b'  from dual union

    select 'CHARLOTE', 'NC', 'b0b'  from dual

    )

    --- END IF SAMPLE Data

    select * from t1 full outer join t2

    on ( nvl(t1.city,t2.city) = nvl(t2.city,t1.city)

    and nvl(t1.state,t2.state) = nvl(t2.state,t1.state) )

    order by 1,2,3,4


    CUSTOMER_ID CITY     STATE CITY      STATE CONTACT

    ----------- -------- ----- --------- ----- -------

              1 SKOKIE   IL              IL    OJO    

              2 CHICAGO  IL              IL    OJO    

              3 CARY     NC                           

              4 ERIE     PA    ERIE            BRITT  

              4 ERIE     PA              PA    MIKE   

              5 PHILLY   PA              PA    MIKE   

              6 CHARLOTE NC    CHARLOTE  NC    b0b    

                               N043      TAT   affi   

                               PITTSBURG PA    HILTON 

                                         B     affi   

                                         Q     b0b    

                                         R     b0b    

     

     

    12 rows selected

     

     

    Elapsed: 00:00:00.112

     

    Hope this is helpful. If not please let us know what is you desired result (sample) if your data was like above?

    vr,

    Sudhakar

  • 13. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    CGomes Newbie
    Currently Being Moderated

    Sorry i miss your point..

     

    Here it is the join that i think you want:

     

    SELECT t.customer_id, t.city, t.state, append(t.contact) contacts

    FROM (

    SELECT t1.customer_id, t1.city, t1.state, t2.contact

    FROM table1 t1, table2 t2

    WHERE REGEXP_LIKE(t1.city||';'||t1.state, '^'||NVL(t2.city,'([0-9a-zA-Z])*')||';'||NVL(t2.state,'([0-9a-zA-Z])*')||'$','i')

    union all

    SELECT t1.customer_id, t1.city, t1.state, null contacts

    FROM table1 t1

    WHERE not exists (select 1 from table2 t2 WHERE t1.city||';'||t1.state = t2.city||';'||t2.state)

    ) t

    GROUP BY customer_id, city, state

    ;

     

    The result is something like this:

     

    customer_idcitystatecontact
    1SKOKIEILOJO
    2CHICAGOILOJO
    3CARYNC
    4ERIEPABRITT, MIKE
    5PHILLYPAMIKE
    6CHARLOTENC
  • 14. Re: SQL Join between two tables two columns, but the data in the join condition could be null.
    799664 Newbie
    Currently Being Moderated

    Hello All....

     

    here are the insert statement and the SQL that is working find.. But I am giving another thought if this can be achieved in another way. Because table1 is supposed to have atleast 300K at any given point. and table2 will remain static with 2K rows.

     

    SET DEFINE OFF;

    Insert into TABLE1

       (CUSTOMER_ID, CITY, STATE)

    Values

       (1, 'K004', 'EST');

    Insert into TABLE1

       (CUSTOMER_ID, CITY, STATE)

    Values

       (8, 'G003', 'COU');

    Insert into TABLE1

       (CUSTOMER_ID, CITY, STATE)

    Values

       (11, 'Q400', 'TVT');

    Insert into TABLE1

       (CUSTOMER_ID, CITY, STATE)

    Values

       (26, '0INS', 'WTS');

    Insert into TABLE1

       (CUSTOMER_ID, CITY, STATE)

    Values

       (28, '0RLM', 'ARM');

    COMMIT;

     

     

    SET DEFINE OFF;

    Insert into TABLE2

       (CITY, CONTACT)

    Values

       ('K008', 'papaJ');

    Insert into TABLE2

       (STATE, CITY, CONTACT)

    Values

       ('TVT', 'Q400', 'david');

    Insert into TABLE2

       (STATE, CONTACT)

    Values

       ('EST', 'earli');

    Insert into TABLE2

       (STATE, CONTACT)

    Values

       ('PHT', 'bob');

    Insert into TABLE2

       (CITY, CONTACT)

    Values

       ('K004', 'earli');

    Insert into TABLE2

       (CITY, CONTACT)

    Values

       ('Q400', 'david');

    Insert into TABLE2

       (STATE, CONTACT)

    Values

       ('WTS', 'watson');

    Insert into TABLE2

       (STATE, CONTACT)

    Values

       ('PRE', 'papaJ');

    Insert into TABLE2

       (STATE, CONTACT)

    Values

       ('ARM', 'wayne');

    COMMIT;

     

     

     

     

     

     

    SELECT T1.CUSTOMER_ID, T1.CITY, T1.STATE, T2.CONTACT

    FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON (T1.CITY=T2.CITY OR T1.STATE=T2.STATE)

    WHERE REGEXP_LIKE(T1.CITY||';'||T1.STATE, '^'||NVL(T2.CITY,'([0-9a-zA-Z])*')||';'||NVL(T2.STATE,'([0-9a-zA-Z])*')||'$','i')

    GROUP BY T1.CUSTOMER_ID, T1.CITY, T1.STATE, T2.CONTACT

    ORDER BY CUSTOMER_ID

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points