CODEWITHSUNDEEP
mysql
user3793272
user3793272

Reputation: 111

Sql many id in one column

I have a table like this

STUDENT table

ID | Name | Subject |
1  | foo  | 1,2,3   |

SUBJECT table

ID | Subject_name |
1  | Math         |
2  | Science      |
3  | History      |

I tried this one

"SELECT * FROM student LEFT JOIN subject ON student.id = subject.id"

But their is an error

what I expected would be to get all subject_name in student table

Upvotes: 2

Views: 597

Answers (5)

Angad Bansode
Angad Bansode

Reputation: 923

You can try this one: select t.subject_name from student s , subject t on s.id = t.id

Upvotes: -1

Harsh Patel
Harsh Patel

Reputation: 47

To get all subject names from subject table for students you need to use FIND_IN_SET and GROUP_CONCAT functions in MySQL. 

SELECT  a.id, a.Name, a.Subject,
        GROUP_CONCAT(b.Subject_name ORDER BY b.id) SubjectName
FROM    student a
        INNER JOIN SUBJECT b
            ON FIND_IN_SET(b.id, a.Subject) > 0
GROUP   BY a.id

You can check my demo here

Upvotes: -1

Salim Ibrohimi
Salim Ibrohimi

Reputation: 1391

It is better to create one more table which will connect student table and subject table e.g. student_subject which will consist of these fields: id, student_id, subject_id; and you should remove the subject field from student table.

Select query:

SELECT * /* fields you need */
FROM student
LEFT JOIN student_subject ON student.id = student_subject.student_id
LEFT JOIN student_subject ON subject.id = student_subject.subject_id

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Use FIND_IN_SET:

SELECT t1.*
FROM subject t1
INNER JOIN student t2
    ON FIND_IN_SET(t1.ID, t2.Subject) > 0
ORDER BY t1.ID;

Note that FIND_IN_SET is not a function which engenders the best database design, because in general CSV data is not well suited to a database table. That being said, you might want to consider breaking out the subjects in the student table into separate rows.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

You need to use FIND_IN_SET to find the value within the range and GROUP_CONCAT to return back the rows in comma separated value.

SELECT  s.id, s.Name, GROUP_CONCAT(t.Subject_name)
FROM    student s
        INNER JOIN subject t
            ON FIND_IN_SET(t.ID, s.subject)
GROUP BY s.id, s.Name

Here's a Demo.

However, consider also changing the schema of your table as currently it's not a desirable design.

Upvotes: 4

Related Questions