SQL query based on another table

Question

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

This database design is broken. Table 1's FID column should not include the value from the FNAME column. It should be identical to table 2's FID column, with a foreign key between the two. Fixing this will make your query (and others) significantly simpler, faster, more robust, easier to maintain and adapt, etc, etc. — MatBailie
– MatBailie, Commented Apr 29, 2022 at 9:54
This site uses English. PS minimal reproducible example How to Ask Help center — philipxy
– philipxy, Commented Apr 29, 2022 at 10:50
I am simplifying a real world scenario using basic example and think I messed up here, the FID in table one is not called FID, it's called something else. — coder787
– coder787, Commented Apr 29, 2022 at 11:02
Please clarify via edits, not comments. See How do comment replies work? to learn to use @x to notify one non-sole non-poster commenter x re a comment. Posters, sole commenters & followers of posts always get notified. — philipxy
– philipxy, Commented Apr 29, 2022 at 11:09

Donal · Accepted Answer · 2022-04-29 11:14:23Z

1

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');

edited Apr 29, 2022 at 11:14

answered Apr 29, 2022 at 9:33

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

coder787 Over a year ago

that's great thanks, totally forgot to say i'm using ORACLE. do you know how to translate the PATINDEX to ORACLE?

Donal Over a year ago

@coder787 For oracle I would use regexp_replace, I have updated the answer.

coder787 Over a year ago

thanks, did you mean to take out the substring? I am getting a lot of matches now

Donal Over a year ago

regexp_replace is the correct oracle function to use

coder787 Over a year ago

ah I think I see the problem, there are also other ids like this "BANANA18" in table 1, the query is also returning this one when it shouldn't, it should only be leading match.

|

MT0 · Accepted Answer · 2022-04-29 11:28:16Z

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

Toni Antunović · Accepted Answer · 2022-04-29 10:58:51Z

0

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

edited Apr 29, 2022 at 10:58

answered Apr 29, 2022 at 10:09

Toni Antunović

5514 silver badges6 bronze badges

2 Comments

coder787 Over a year ago

thanks, but I'm getting "V.FID" is invalid identifier.

Toni Antunović Over a year ago

It shouldn't be invalid identifier because as you see the subquery: (SELECT TO_NUMBER(REGEXP_SUBSTR(FID,'^[0-9]{1,}'),'9999') FID,FNAME,ID1 FROM TABLE1)V has a FID column, try to add AS to: TO_NUMBER(REGEXP_SUBSTR(FID,'^[0-9]{1,}'),'9999') AS FID. I updated my code so u can copy and past it again

Barbaros Özhan · Accepted Answer · 2022-04-29 11:16:27Z

0

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')

Demo

answered Apr 29, 2022 at 11:16

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL query based on another table

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related