How to join tables in SQL based on the result of the first query?

Question

Oracle

SELECT REGEXP_SUBSTR(listinfo, ';[^;]+') as list
  FROM import AS i
  JOIN lists as l ON l.listid = list
  WHERE i.userid = 1

We get an error that the list column is unknown because the 'list' column does not exist (this column is the result of the query). The result of 'list' is not a single value, but several at once. Is there a way to write a query in a query? First make a request that gets the data and then make another request based on the first request.

Answer 1

SELECT *
FROM
(
  SELECT REGEXP_SUBSTR(listinfo, ';[^;]+') as list
  FROM import
) AS i
JOIN lists as l 
ON l.listid = list
WHERE i.userid = 1

Answer 2

The correct option would be to normalize the `import` table and move the many-to-many relationship to a separate table.
Now your regex will return only one occurrence of listinfo for each line, and even then with a semicolon at the beginning. For example, for the string '1;2;3' your REGEXP_SUBSTR will only return ';2'.