Mysql Join键上的两个表 [英] Mysql Join Two tables on keys

问题描述

我试图连接两个表，但是似乎有一个问题，我正在寻找的输出是有主题的行数.

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'

keys2表如下:

id List Idsubjek  Subject
1  52             Maths
2  53             Geography

教育表格如下:

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.

任何帮助将不胜感激.

推荐答案

首先，您应该规范化数据结构并在keys2和教育表之间建立一个连接表.

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

要使连接与当前数据结构一起使用，请使用mysql的

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'

这篇关于Mysql Join键上的两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！