I have two tables like below.
Table 1:
SELECT '1234' JOBNO, 'N111' NID ,'X W111 1-N-**-20 DWSTFDWHEQC' NOTE FROM DUAL UNION ALL
SELECT '1234' JOBNO, 'N112' NID ,'W112 G-DFS XWRSVSFSGBGHDNDHJG' NOTE FROM DUAL UNION ALL
SELECT '1234' JOBNO, 'N113' NID ,'W11335-C-X-**-56TVBSCA54EWE' NOTE FROM DUAL UNION ALL
SELECT '1234' JOBNO, 'N114' NID ,'TRVBRTGBVRAYWCBCDWG' NOTE FROM DUAL UNION ALL
SELECT '1234' JOBNO, 'N115' NID ,'QWFRCTGNILPBHTYTFT' NOTE FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'N116' NID ,'W1146-N-H-**-46' NOTE FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'N117' NID ,'YGEWD W115 7-D-Y-**-11 DFC' NOTE FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'N118' NID ,'1-C-E-**-22' NOTE FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'N119' NID ,'WRD W116 UHBY' NOTE FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'N120' NID ,'XEDXS W1177HI6HB' NOTE FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'N121' NID ,'W-GHRDE FGVD' NOTE FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'N122' NID ,'LKMRF' NOTE FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'N123' NID ,'ECFT W119-8-C-N-**-57 GVG' NOTE FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'N124' NID ,'FRCYIKLOKJFDTR' NOTE FROM DUAL;
Table 2
SELECT '1234' JOBNO, 'W111' WNO,'1-N-**-20' WID FROM DUAL UNION ALL
SELECT '1234' JOBNO, 'W112' WNO,'G-DFS' WID FROM DUAL UNION ALL
SELECT '1234' JOBNO, 'W113' WNO,'35-C-X-**-56' WID FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'W114' WNO,'6-N-H-**-46' WID FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'W115' WNO,'7-D-Y-**-11' WID FROM DUAL UNION ALL
SELECT '2121' JOBNO, 'W116' WNO,'1-C-E-**-22' WID FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'W117' WNO,'99-N-**-87' WID FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'W118' WNO,'W-GHRDE' WID FROM DUAL UNION ALL
SELECT '3131' JOBNO, 'W119' WNO,'8-C-N-**-57' WID FROM DUAL;
- TABLE1 and TABLE2 has a common column JOBNO.
- I need to see all the records of TABLE1, along with two columns (WNO, WID) from TABLE2.
- WNO and WID should be displayed if the any part of the text in TABLE1.NOTE is equal to any WNO or WID with the same JOBNO in TABLE2.
Now using these above 2 tables i am trying to achieve below expected result. Could you please help me out how it can be achieved using only SQL query.
