I have tables like this:
table 1:
FNAME | ID1 | FID |
---|---|---|
BREAD | XYZ | 18BREAD |
FISH | ABC | 45FISH |
BREAD_OLD | BNQ | 18BREAD_OLD |
BACON | TBG | 77BACON |
EGGS | CGS | 99EGGS |
BANANA | BHG | BANANA18 |
table 2:
FNAME | FID |
---|---|
BREAD | 18 |
FISH | 45 |
BACON | 77 |
EGGS | 99 |
currently a simple search is done on table 1 to find id's of food as follows:
SELECT ID1
FROM TABLE1
WHERE NAME IN NAME_LIST
so for example when the name list is: ('BREAD','FISH') then it returns XYZ, ABC. The problem is this misses 'BREAD_OLD' which is an older version with the same ID. (18)
I need to change this so now the search is done based on the FID of food rather than the NAME to find affected foods but I cannot change the input.
eg: given a list of foods: ('BREAD', 'FISH')
the result should be XYZ, ABC, BNQ (because BREAD matches 18 which matches BNQ in table 1)
how can this can be done? I think I need to use a join or do a 'select within a select' but I'm not sure how this would work with multiple inputs.
edit: ORACLE is the database edit 2: adding BANANA18 to table 1, needs to be leading match
CodePudding user response:
The correct solution would be to link on the FID fields. However, the FID field on table1 seems to concatenate the ID with the name. The solution would therfore be to extract the numeric value from that field and then use that to link it to the FID field on table 2. For example:
SELECT t1.FNAME, t1.ID1
FROM table1 t1
INNER JOIN table2 t2 ON t2.FID = regexp_replace(t1.FID, '^[^0-9]', '')
WHERE
t2.FNAME IN ('BREAD','FISH');
CodePudding user response:
Assuming that table1.FID
equals table2.FID
concatenated with table1.FNAME
then you do not need (slow) regular expressions and can use a simple equality combined with string concatenation:
SELECT t1.FNAME,
t1.ID1
FROM table1 t1
INNER JOIN table2 t2
ON t1.FID = t2.fid || t1.fname
WHERE t2.FNAME IN ('BREAD','FISH');
Which, for the sample data:
CREATE TABLE table1 (FNAME, ID1, FID) AS
SELECT 'BREAD', 'XYZ', '18BREAD' FROM DUAL UNION ALL
SELECT 'FISH', 'ABC', '45FISH' FROM DUAL UNION ALL
SELECT 'BREAD_OLD', 'BNQ', '18BREAD_OLD' FROM DUAL UNION ALL
SELECT 'BACON', 'TBG', '77BACON' FROM DUAL UNION ALL
SELECT 'EGGS', 'CGS', '99EGGS' FROM DUAL UNION ALL
SELECT 'BANANA', 'BHG', 'BANANA18' FROM DUAL UNION ALL
SELECT 'TOAST', 'TST', 'TOAST181' FROM DUAL;
CREATE TABLE table2 (FNAME, FID) AS
SELECT 'TOAST', 181 FROM DUAL UNION ALL
SELECT 'BREAD', 18 FROM DUAL UNION ALL
SELECT 'FISH', 45 FROM DUAL UNION ALL
SELECT 'BACON', 77 FROM DUAL UNION ALL
SELECT 'EGGS', 99 FROM DUAL;
Outputs:
FNAME ID1 BREAD XYZ BREAD_OLD BNQ FISH ABC
db<>fiddle here
CodePudding user response:
Sample data:
SELECT * FROM TABLE1;
FNAME ID1 FID
-------------------------
BREAD XYZ 18BREAD
FISH ABC 45FISH
BACON TBG 77BACON
EGGS CGS 99EGGS
BREAD_OLD BNQ 18BREAD_OLD
SELECT * FROM TABLE2;
FNAME FID
----------
BREAD 18
FISH 45
BACON 77
EGGS 99
Query:
In query we join TABLE1 and TABLE2 on FID (using REGEX_SUBSTR to extract first n numbers of TABLE1 FID) so row 'BREAD_OLD' will be joined with row 'BREAD' from TABLE2 and when we add condition FNAME IN ('BREAD') both ID1 of 'BREAD' AND 'BREAD_OLD' will be selected.
SELECT ID1
FROM
(SELECT
TO_NUMBER(REGEXP_SUBSTR(FID,'^[0-9]{1,}'),'9999') AS FID,FNAME,ID1
FROM TABLE1)V
JOIN TABLE2 T
ON (V.FID=T.FID)
WHERE T.FNAME IN ('BREAD','FISH')
Result:
ID1
---
XYZ
ABC
BNQ
CodePudding user response:
One option is joining the tables by matching concatenated columns of table2
with the extracted substrings upto _
character for fid
column of table1
such as
SELECT id1
FROM table1 t1
JOIN table2 t2
ON REGEXP_SUBSTR(t1.fid,'[^_] ') = t2.fid||t2.fname
WHERE t2.fname IN ('BREAD','FISH')