Mysql Join Two tables on keys

Question

I am trying to join two tables, but seem to be having an issue, The output I am looking for is the amount of rows that there is subjects.

SELECT *
FROM education AS a
JOIN keys2 AS b USING(`List Idsubjek`)
WHERE `List Idsubjek` IN (52, 54, 55, 67)
AND `studentid` = '$id'

The keys2 table looks like this:

id List Idsubjek  Subject
1  52             Maths
2  53             Geography

The education table looks like this:

id List Idsubjek       studentid school
1  52,53,54,74,0,0,0   15        school name

What I currently have produces only one row, with an extra column "subject" but it only does the first one. Mathematics.

Any help would be appreciated.

Answer 1

First of all, you should normalise your data structure an have a connection table between keys2 and education table.

To make the join work with the current data structure, use mysql's find_in_set() function:

SELECT *
FROM education AS a
JOIN keys2 AS b on find_in_set(b.`List Idsubjek`,a.`List Idsubjek`)>0 
WHERE b.`List Idsubjek` IN (52, 54, 55, 67)
AND `studentid` = '$id'