OraFAQ Forum: SQL & PL/SQL » Two Column Join

Home » SQL & PL/SQL » SQL & PL/SQL » Two Column Join (Oracle PL SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Two Column Join [message #686859]

Fri, 27 January 2023 07:19

ibrahimzuberi
Messages: 3
Registered: January 2023

Junior Member

Hi I have two tables which contains data like below.

Col1 Col2
00220 002207180441
0022070 002207520355
0022075 002207724613
0022076 002207803410
0022077
00220770
00220775
00220776
00220777
00220778
00220779
0022078
0022079
00220810
00220820
002209

These colours should match exactly, please provide me Query for this.

Report message to a moderator

Re: Two Column Join [message #686860 is a reply to message #686859]

Fri, 27 January 2023 07:47

Michel Cadot
Messages: 68415
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Two Column Join [message #686861 is a reply to message #686860]

Fri, 27 January 2023 08:04

ibrahimzuberi
Messages: 3
Registered: January 2023

Junior Member

Hi, as you can see that I have two table columns, one column contains half values and the other contains full values. I like to have results with the best possible matches. I tried following but it didnt work.

col2 like col1 || '%'

and another try

col1 = substr(col2, 1, LENGTH(col1))

but it is not matching precisly what I am looking for. I want big value first to be compaired as compare to lower values of col1

Report message to a moderator

Re: Two Column Join [message #686862 is a reply to message #686861]

Fri, 27 January 2023 09:04

Michel Cadot
Messages: 68415
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post what is asked.

Report message to a moderator

Re: Two Column Join [message #686868 is a reply to message #686861]

Fri, 27 January 2023 11:23

Barbara Boehmer
Messages: 8985
Registered: November 2002
Location: California, USA

Senior Member

It looks like your where clauss should produce the desired results, so you need to provide the things that have already been asked for and a better explanation of what it is that you want instead of what you are getting. Please see the following simulation using Oracle demo data that produces what you seem to be asking for using your where clauses.

SCOTT@orcl_12.1.0.2.0> create table tab1 as select ename, comm col1 from emp where comm is not null
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> create table tab2 as select ename, sal col2 from emp where sal is not null
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> select * from tab1
  2  /

ENAME            COL1
---------- ----------
ALLEN             300
WARD              500
MARTIN           1400
TURNER              0

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from tab2
  2  /

ENAME            COL2
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

SCOTT@orcl_12.1.0.2.0> select tab1.ename, tab1.col1, tab2.ename, tab2.col2 from tab1, tab2
  2  where  tab2.col2 like tab1.col1 || '%'
  3  /

ENAME            COL1 ENAME            COL2
---------- ---------- ---------- ----------
ALLEN             300 SCOTT            3000
ALLEN             300 FORD             3000
WARD              500 KING             5000

3 rows selected.

SCOTT@orcl_12.1.0.2.0> select tab1.ename, tab1.col1, tab2.ename, tab2.col2 from tab1, tab2
  2  where tab1.col1 = substr(tab2.col2, 1, LENGTH(tab1.col1))
  3  /

ENAME            COL1 ENAME            COL2
---------- ---------- ---------- ----------
ALLEN             300 SCOTT            3000
ALLEN             300 FORD             3000
WARD              500 KING             5000

3 rows selected.

Report message to a moderator

Re: Two Column Join [message #686876 is a reply to message #686859]

Sat, 28 January 2023 06:32

Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA

Senior Member

ibrahimzuberi wrote on Fri, 27 January 2023 08:19

These colours should match exactly, please provide me Query for this.

There is no such thing as colors is SQL. And it isn't clear if col1 is unique. I'll assume it is. Then:

with sample as (
                select '00220' col1,'002207180441' col2 from dual union all
                select '0022070','002207520355' from dual union all
                select '0022075','002207724613' from dual union all
                select '0022076','002207803410' from dual union all
                select '0022077',null from dual union all
                select '00220770',null from dual union all
                select '00220775',null from dual union all
                select '00220776',null from dual union all
                select '00220777',null from dual union all
                select '00220778',null from dual union all
                select '00220779',null from dual union all
                select '0022078',null from dual union all
                select '0022079',null from dual union all
                select '00220810',null from dual union all
                select '00220820',null from dual union all
                select '002209',null from dual
               )
select  s1.*,
        (
         select  s2.col1
           from  sample s2
           where instr(s1.col2,s2.col1) = 1
           order by length(s2.col1) desc
          fetch first 1 row only
        ) matching_col1
  from  sample s1
  order by s1.col1
/


COL1     COL2                 MATCHING_COL1
-------- -------------------- -------------
00220    002207180441         00220
0022070  002207520355         0022075
0022075  002207724613         0022077
0022076  002207803410         0022078
0022077
00220770
00220775
00220776
00220777
00220778
00220779
0022078
0022079
00220810
00220820
002209

16 rows selected.

SQL>

SY.

Report message to a moderator

Re: Two Column Join [message #686877 is a reply to message #686876]

Sat, 28 January 2023 16:36

Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just for fun, spooling to HTML.

Script matches.sql:

set verify off
set trimspool on
set trimout on
set feedback off
set heading on
set echo off
set pages 150
set termout off
set markup html on spool on entmap off
set echo off
spool c:\temp\matches.html
with sample as (
                select '00220' col1,'002207180441' col2 from dual union all
                select '0022070','002207520355' from dual union all
                select '0022075','002207724613' from dual union all
                select '0022076','002207803410' from dual union all
                select '0022077',null from dual union all
                select '00220770',null from dual union all
                select '00220775',null from dual union all
                select '00220776',null from dual union all
                select '00220777',null from dual union all
                select '00220778',null from dual union all
                select '00220779',null from dual union all
                select '0022078',null from dual union all
                select '0022079',null from dual union all
                select '00220810',null from dual union all
                select '00220820',null from dual union all
                select '002209',null from dual
               ),
    matches as (
                select  s1.*,
                        s2.col1 matching_col1
                  from  sample s1,
                        sample s2
                  where instr(s1.col2,s2.col1) = 1
                  order by dense_rank() over(partition by s1.col2 order by length(s2.col1) desc)
                  fetch first 1 row with ties
               ),
matches_clr as (
                select  m.*,
                        case row_number() over(order by m.col1)
                          when 1 then 'Yellow'
                          when 2 then 'Red'
                          when 3 then 'Blue'
                          when 4 then 'Green'
                          else 'White'
                        end match_color
                  from  matches m
               )
select  '<span style="color:' || m1.match_color || ';">' || s.col1 || '</span>' col1,
        '<span style="color:' || m2.match_color || ';">' || substr(s.col2,1,length(m2.matching_col1)) || '</span>' ||
        '<span>' || substr(s.col2,nvl(length(m2.matching_col1),0) + 1) || '</span>' col2
  from  sample s,
        matches_clr m1,
        matches_clr m2
  where m1.matching_col1(+) = s.col1
    and m2.col1(+) = s.col1
  order by s.col1
/
spool off

Now:

I:\>sqlplus scott@sol19pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 28 17:35:10 2023
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Sat Jan 28 2023 17:28:50 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> @matches.sql
SQL>

Attached is color-coded html spool file. Obviously it will work correctly only if there are no more than four matches since it assigns only four colors. OP will need to come up with colors for additional matches.

SY.

Attachment: matches.html
(Size: 3.19KB, Downloaded 293 times)

[Updated on: Sat, 28 January 2023 16:42]

Report message to a moderator

Re: Two Column Join [message #686879 is a reply to message #686877]

Sun, 29 January 2023 05:29

Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA

Senior Member

I missed col1 can match more than one col2. For example:

00220 002207180441
0022070 002207520355
0022075 002207724613
0022076 002207803410
0022077
00220770
00220775
00220776
00220777
00220778
00220779
0022078
0022079
00220810
00220820
002209
0032070 002207540355

Adjusted matches.sql:

set verify off
set trimspool on
set trimout on
set feedback off
set heading on
set echo off
set pages 150
set termout off
set markup html on spool on entmap off
set echo off
spool c:\temp\matches.html
with sample as (
                select '00220' col1,'002207180441' col2 from dual union all
                select '0022070','002207520355' from dual union all
                select '0022075','002207724613' from dual union all
                select '0022076','002207803410' from dual union all
                select '0022077',null from dual union all
                select '00220770',null from dual union all
                select '00220775',null from dual union all
                select '00220776',null from dual union all
                select '00220777',null from dual union all
                select '00220778',null from dual union all
                select '00220779',null from dual union all
                select '0022078',null from dual union all
                select '0022079',null from dual union all
                select '00220810',null from dual union all
                select '00220820',null from dual union all
                select '002209',null from dual union all
                select '0032070','002207540355' from dual
               ),
    matches as (
                select  s1.*,
                        s2.col1 matching_col1
                  from  sample s1,
                        sample s2
                  where instr(s1.col2,s2.col1) = 1
                  order by dense_rank() over(partition by s1.col2 order by length(s2.col1) desc)
                  fetch first 1 row with ties
               ),
matches_clr as (
                select  m.*,
                        case dense_rank() over(order by m.matching_col1)
                          when 1 then 'Yellow'
                          when 2 then 'Red'
                          when 3 then 'Blue'
                          when 4 then 'Green'
                        end match_color,
                        row_number() over(partition by m.matching_col1 order by m.col1) rn
                  from  matches m
               )
select  '<span style="color:' || m1.match_color || ';">' || s.col1 || '</span>' col1,
        '<span style="color:' || m2.match_color || ';">' || substr(s.col2,1,length(m2.matching_col1)) || '</span>' ||
        '<span>' || substr(s.col2,nvl(length(m2.matching_col1),0) + 1) || '</span>' col2
  from  sample s,
        matches_clr m1,
        matches_clr m2
  where m1.matching_col1(+) = s.col1
    and m1.rn(+) = 1
    and m2.col1(+) = s.col1
  order by s.col1
/
spool off

Color-coded html spool file is attached.

SY.

Attachment: matches.html
(Size: 3.33KB, Downloaded 407 times)

Report message to a moderator

Re: Two Column Join [message #686890 is a reply to message #686879]

Sun, 29 January 2023 17:36

mathguy
Messages: 37
Registered: January 2023

Member

You say you have two tables, but you show the sample data in two columns, COL1 and COL2, as if they were in a single table. That is what some responders assumed.

I guess you really do have two tables; they may be called TABLE_1 and TABLE_2, and the columns might be named - to be more descriptive - something like STUB for the shorter strings in TABLE_1 and STR for the "full" strings in TABLE_2. Also, for proper testing, you should always include NULL, at least in TABLE_2, as well as a non-NULL STR that doesn't match any STUB. Presumably you will want all STR to appear in the output, with LONGEST_STUB shown as NULL if STR doesn't match any STUB.

Input data may look like this:

select * from table_1;

STUB                
--------------------
00220
0022070
0022075
0022076
0022077
00220770
00220775
00220776
00220777
00220778
00220779
0022078
0022079
00220810
00220820
002209

select * from table_2;


STR                                     
----------------------------------------
002207180441
002207520355
002207724613
002207803410

33330290

Then the query and the output might look like this:

select str, (select max(stub) keep (dense_rank last order by length(stub)) from table_1 where instr(str, stub) = 1) as longest_stub
from   table_2;

STR                                      LONGEST_STUB        
---------------------------------------- --------------------
002207180441                             00220               
002207520355                             0022075             
002207724613                             0022077             
002207803410                             0022078             
                                                             
33330290

Report message to a moderator

Re: Two Column Join [message #686892 is a reply to message #686890]

Mon, 30 January 2023 00:30

ibrahimzuberi
Messages: 3
Registered: January 2023

Junior Member

thank you @mathguy
its a solution and working properly

Report message to a moderator

Previous Topic:	greatest total amount of purchases per day for each customer_id
Next Topic:	REST API with DDOS protection

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 21 21:19:17 CDT 2023