-1

There are many questions like this but didn't helped me I have multiple tables that contain different data. There is an institutes table that contains multiple institutes

institutes

id name
1 A
2 B

I'm using the id of institutes in the courses table

courses

id course inst_id
1 A 1,2
2 B 1

Now i want to select institute name based on courses.inst_id, it return B when courses.id = 2 that's good but when id = 1 it returns only one institute name but inst_id contains on 1,2 so it should be returned both values A and B is there is any way to do that?

Here is my try

$match = '1,2';
    $stmt = $db->prepare("SELECT courses.*, teachers.name,i.name as institute FROM courses 
    JOIN teachers ON teachers.id =  courses.teacher_id JOIN institutes i WHERE i.id IN 
    ($match) AND courses.id = :id ");
    $stmt->execute([':id' => $id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
Atif Raja
  • 17
  • 4

1 Answers1

0

Save inst_id as a string such as '1','2'

Check this query

SELECT courses.*, teachers.name,i.name as institute 
FROM courses 
JOIN teachers ON teachers.id =  courses.teacher_id JOIN institutes i WHERE i.id IN 
(SELECT data_set.inst_id FROM (select inst_id from courses) AS data_set) AND courses.id = :id

Make necessary changes the query where needed.

Mainul Hasan
  • 691
  • 3
  • 12