Left join with an id (number) on id in a String-CodePudding

I have two tables like this

Table Person

id	name
10	Max
11	Ben
12	Luke

Table Bike (the person_id column is a varchar)

bike_id	bike_name	Person_id
1	Hercules	10
2	Linx	11, 12

Now I need a table like this

id	name	bike_id	bike_name
10	Max	1	Hercules
11	Ben	2	Linx
12	Luke	2	Linx

I tried it with this SQL:

SELECT *  From Person person
 LEFT JOIN Bike bike
  ON (cast(person.id as varchar(10)) = bike.id);

And receive this table:

id	name	bike_id	bike_name
10	Max	1	Hercules

I am using Oracle database.

CodePudding user response：

It is always a bad idea storing values as you do (talking about bike.person_id column).

One option to do what you want is to split person_id into rows and then perform join:

Sample data:

SQL> with
  2  person (id, name) as
  3    (select 10, 'Max'  from dual union all
  4     select 11, 'Ben'  from dual union all
  5     select 12, 'Luke' from dual
  6    ),
  7  bike (bike_id, bike_name, person_id) as
  8    (select 1, 'Hercules', '10'    from dual union all
  9     select 2, 'Linx'    , '11,12' from dual
 10    )
 11  --

Query begins here:

 12  select p.id, p.name, x.bike_id, x.bike_name
 13  from person p join
 14    (select b.bike_id, b.bike_name,
 15       regexp_substr(b.person_id, '[^,] ', 1, column_value) person_id
 16     from bike b cross join
 17       table(cast(multiset(select level from dual
 18                           connect by level <= regexp_count(b.person_id, ',')   1
 19                          ) as sys.odcinumberlist))
 20    ) x on x.person_id = p.id;

        ID NAME    BIKE_ID BIKE_NAM
---------- ---- ---------- --------
        10 Max           1 Hercules
        11 Ben           2 Linx
        12 Luke          2 Linx

SQL>

CodePudding user response：

You could use the following join trick:

SELECT p.id, p.name, b.bike_id, b.bike_name
FROM Bike b
INNER JOIN Person p
    ON ',' || b.Person_id || ',' LIKE '%,' || CAST(p.id AS varchar(10)) || ',%';

By the way, you should avoid storing CSV in the persons column.

Edit: If the Person_id column happens to be using comma followed by space as the separator, then use this version:

SELECT p.id, p.name, b.bike_id, b.bike_name
FROM Bike b
INNER JOIN Person p
    ON ',' || REPLACE(b.Person_id, ' ', '') || ',' LIKE '%,' || CAST(p.id AS varchar(10)) || ',%';

CodePudding user response：

 with newbike as
(
SELECT    bike_id,bike_name, 
REGEXP_SUBSTR (STR, '[^,] ', 1, LEVEL) SPLIT_VALUES  FROM bike 
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (person_id, ',', NULL)) 
FROM TAB)
);

SELECT *  From Person person
 LEFT JOIN newbike bike
  ON person.id = bike.SPLIT_VALUES;

we can create new table with split value using this