How to display only matched items using JOIN

Question

Can someone help me to make SQL (ORACLE) query more flexible using such conditions?

There are 2 tables:

Table 1

CodeA      Year   

1112-999   2017
1113-999   2017

Table 2

CodeB      Year   

1114-111   2017
1115-111   2018

Then run this script

SELECT aa.CodeA, bb.CodeB FROM Table 1 aa
JOIN Table 2 bb ON aa.Year = bb.Year
AND REGEXP_LIKE (aa.CodeA, '^111+2');

In this case the result is:

CodeA      CodeB   

1112-999   1114-111

But in this case it needs to show up only the 1st value and another column should be empty after running script.

For example,

CodeA      CodeB   

1112-999   NULL

But if column "CodeB" (Table 2) contains value = 1112-534, then that value should appear after running the same script

For example,

Table 1

CodeA      Year   

1112-999   2017
1113-999   2017

Table 2

CodeB      Year   

1114-111   2017
1112-534   2017
1115-111   2018

And after running the same script, the result should be:

CodeA      CodeB   

1112-999   1112-534

What should I change in this script in order to make it flexible (universal) depending on input data in table? I mean when Table 2 does not contain expected value (in this case 1112-534), then the result (for 2nd column) should be NULL:

CodeA      CodeB   

1112-999   NULL

and when Table 2 contains this value (1112-534), then the response should be:

CodeA      CodeB   

1112-999   1112-534

Here is the script I have and want to make it more flexible:

SELECT aa.CodeA, bb.CodeB FROM Table 1 aa
JOIN Table 2 bb ON aa.Year = bb.Year
AND REGEXP_LIKE (aa.CodeA, '^111+2');

Help me please))) Thanks in advance.

Please paste plain text from SQL*plus, rather than HTML which renders clumsily on stackoverflow. Simple `DESCRIBE table` output would be much more helpful than the you posted. — J_H, Aug 12 '17 at 15:27

J_H · Accepted Answer · 2017-08-12T19:27:15.433

1

You may find it helpful to view the join results prior to filtering:

SELECT aa.CodeA, bb.CodeB FROM Table1 aa
JOIN Table2 bb ON aa.Year = bb.Year;

Your English language specification is not quite clear, but it suggests that you want NULL filtering:

SELECT aa.CodeA, bb.CodeB FROM Table1 aa
JOIN Table2 bb ON aa.Year = bb.Year
WHERE REGEXP_LIKE(aa.CodeA, '^1+2')
and bb.CodeB is not null;

See also https://en.wikipedia.org/wiki/Join_(SQL)

edited Aug 12 '17 at 19:27

answered Aug 12 '17 at 15:39

J_H

17,926
4
24
44

Just edited it. Sorry for so ugly description. That my 1st question using tables. I thought that table would be auto-edited. I also updated description as well. Can you please review once again. I tried to express my thoughts more clearly))) – Vladimir Aug 12 '17 at 18:44
Please take a look at the documented JOIN variants, especially https://en.wikipedia.org/wiki/Join_(SQL)#Outer_join left outer join, and then view your JOIN without WHERE clause regex filtering. Post any JOIN results that appear promising. First you need to identify an appropriate JOIN relation, and then you can filter it down to just the relevant rows. – J_H Aug 12 '17 at 19:32
Thanks. I think left outer join will solve my issue. – Vladimir Aug 12 '17 at 19:53
Thanks a lot for pointing out the correct way to solve my issue. It needs to use "LEFT OUTER JOIN" and also "WHERE REGEXP_LIKE" (in my case). Here is the correct script: SELECT aa.CodeA, bb.CodeB FROM Table1 aa JOIN Table2 bb ON bb.CodeB LIKE CONCAT(aa.CodeA ,'%') WHERE REGEXP_LIKE(aa.CodeA, '^1+2'); – Vladimir Aug 13 '17 at 06:35

How to display only matched items using JOIN

1 Answers1