CODEWITHSUNDEEP
phpmysql
NFTtray
NFTtray

Reputation: 75

I want to join two table using find_in_set

1) student_table

 student_id  student_name
 100         yash
 101         rahul
 102         jay
 103         roy

2) stop_table

stop    student_id
abc     100,102
def     101,103

This is my request

SELECT
  student_table.student_name,
  stop_table.stop           
  FROM student_table
  JOIN stop_table ON 
  FIND_IN_SET(student_table.student_id,'101,102')

It is not giving stop of that particular student_id result =>

rahul abc
rahul def
jay   abc 
jay   def

Upvotes: 1

Views: 46

Answers (2)

Nick
Nick

Reputation: 147216

Your JOIN condition doesn't make any sense as it doesn't restrict the selection of values from stop_table relative to those from student_table, so you are essentially getting a CROSS JOIN between student_table and stop_table. That is why you are getting multiple rows for each student. What you actually need is to JOIN on 

FIND_IN_SET(student_table.student_id, stop_table.student_id)

and put your JOIN condition in as a WHERE condition to select only students with ids 101 & 102:

SELECT s.student_name, t.stop
FROM student_table s
JOIN stop_table t ON FIND_IN_SET(s.student_id, t.student_id)
WHERE s.student_id IN (101, 102)

Output:

student_name    stop
rahul           def
jay             abc

Note I've written the WHERE clause slightly differently but it could be written as

WHERE FIND_IN_SET(s.student_id, '101,102')

Demo on dbfiddle

Upvotes: 1

Jumpei
Jumpei

Reputation: 621

SELECT
  student_table.student_name,
  stop_table.stop           
  FROM student_table
  JOIN stop_table ON 
  FIND_IN_SET(student_table.student_id, stop_table.student_id)

Upvotes: 0

Related Questions