8

How can I join two tables, where one of the tables has multiple comma separated values in one column that reference an id in another column?

1st table 

Name    | Course Id
====================
Zishan  | 1,2,3                                           
Ellen   | 2,3,4

2nd table

course id | course name 
=======================
   1      |  java
   2      |  C++
   3      |  oracle
   4      |  dot net
Tanner
  • 22,205
  • 9
  • 65
  • 83
md zishan Rashid
  • 83
  • 1
  • 1
  • 3
  • 4
    Please normalized your DB structure! – Joe Taras Oct 07 '14 at 12:51
  • When you combine multiple values into a single field, they become a single value. Perhaps you can create a view which parses the data into an actual relational table and then perform your join with that view? – David Oct 07 '14 at 12:52
  • normalize like this (Zishan,1),(Zishan,2),(Zishan,3) and (Ellen ,2),(Ellen ,3),(Ellen ,4) – Dgan Oct 07 '14 at 12:55
  • 1
    Normalising your data would be the better option, but there is an answer here that should help you http://stackoverflow.com/questions/13527537/sql-query-to-split-column-data-into-rows – Ian Kenney Oct 07 '14 at 12:55
  • 4
    This is a database design error. It is critical that you fix it and not use a hack to get the data out. This will permanently affect performance and it will get much worse as your data grows. Basically this is a cancer at the heart of your system and if you do not fix it, then you will find that over time the problem will get larger and much much harder to fix. You should not allow this serious design flaw to stand unless this is software bought from a third party and database design this incompetent would lead me to stop using any software that company designed. – HLGEM Oct 07 '14 at 14:12
  • Possible duplicate of [join comma delimited data column](https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column) – philipxy Aug 14 '18 at 20:16

4 Answers4

19

Maybe this uglyness, I have not checked results:

select names.name, courses.course_name
from names inner join courses
    on ',' + names.course_ids + ',' like '%,' + cast(courses.course_id as nvarchar(20)) + ',%'
Arvo
  • 10,349
  • 1
  • 31
  • 34
  • 4
    For all that this is an ugly hack, I love that it is an incredibly simple way of getting the desired results. For people who don't have the option of normalising the database schema, this is a great workaround. – Alex Jan 25 '16 at 05:22
2

First of all your Database structure is not normalized and should have been. Since it is already set up this way , here's how to solve the issue.

You'll need a function to split your string first:

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255)

     RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
            LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1), delim, '');

Then you'll need to create a view in order to make up for your structure:

CREATE VIEW database.viewname AS 
    SELECT SPLIT_STRING(CourseID, ',', n) as firstField,
           SPLIT_STRING(CourseID, ',', n) as secondField,
           SPLIT_STRING(CourseID, ',',n) as thirdField 
    FROM 1stTable;

Where n is the nth item in your list.

Now that you have a view which generates your separated fields, you can make a normal join on your view, just use your view like you would use a table.

SELECT * 
FROM yourView 
JOIN table1.field ON table2.field

However since I don't think you'll always have 3 values in your second field from your first table you'll need to tweak it a little more.

Inspiration of my answer from:

SQL query to split column data into rows and Equivalent of explode() to work with strings in MySQL

Brad
  • 359
  • 5
  • 21
Jeredepp
  • 222
  • 1
  • 8
0

SELECT f.name,s.course_name FROM table1 AS f INNER JOIN table2 as s ON f.course_id IN (s.course_id)

Zahid Gani
  • 169
  • 6
-4

Use the Below Query For Solution

Select * from table_2 t2 INNER JOIN table_1 t1 on t1.Course Id = t2.course id
Trushal Makwana
  • 9
  • 3