CODEWITHSUNDEEP
phpmysqlsql
Rami ZK
Rami ZK

Reputation: 560

How to put more than one relational id in one column mysql?

suppose that I have two tables first of them is : 

   Course table  
   ----------------
   |id | name     |
   |0  | pr course|
   |1  | science  |
   ----------------

and the other is : 

Teacher table 
----------------------
|id | name | Courses |
|0  | mark | 0 ,1    |
|1 | john | 0        |
----------------------

I want to put in the course column the id's of courses which the teacher gives to use them in join statement . what is the best method to do something like that . I hope that i was clear enough and thank you.

Upvotes: 1

Views: 202

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

This is terrible way to store comma-separated values, but if you don't have ability to change It, you could try something like.

QUERY 

select t.name as TeacherName, c.name as CourseName
from Teacher t
inner join Courses c
    on ',' + t.id + ',' like '%,' + cast(c.id as nvarchar(20)) + ',%'

SAMPLE DATA

create table Courses
(
    id nvarchar(60),
    name nvarchar(60)
)
insert into Courses values (0 ,'pr course'), (1 ,'science')     

create table Teacher
(
   id nvarchar(60),
   name nvarchar(60),
   courses nvarchar(60)
)
insert into Teacher values (0,'mark','0,1'), (1,'john','0'  )

OUTPUT

TeacherName     CourseName
mark            pr course
john            science*

Upvotes: 0

foobar443
foobar443

Reputation: 2677

The way you are designing your database breaks the First Normal Form which says that every attribute of your database should be atomic. Typically, the Coursescolumn is not atomic because it is a list.

You need to create a new table like GivenBy that look like

   GivenBy table  
   ------------------------
   |course_id | teacher_id|
   ------------------------

In this table, the Primary Key is composed of the 2 columns (course_id and teacher_id). Each row stores one relation between a teacher and a course. Finally, you should have Foreign Keys referencing the id's of Course and Teacher

Upvotes: 5

elRon79
elRon79

Reputation: 86

You might just want to add a third table which handles the teacher-course Relation.

TeachersCourses table
----------------------
|teacher_id|course_id|
----------------------

Then you can use your join-statement on this table. Don't try to put more then one value (especially no keys) in one column.

Upvotes: 0

Related Questions