how to get the count of matches between columns in two tables in mysql

Question

I am trying to figure out how to go about with this scenario in mySQL -

There are three tables - t_student, t_teacher, t_result structure of the tables (basically columns in each of them) are as below:

t_student:
s_id, s_name, s1, s2, s3

where s_id is the primary key, s_name is student name, (s1,s2,s3) are subjects like math, phy, chem etc..

t_teacher:
t_id, t_name, s1, s2, s3

where t_id is the primary key, t_name is teacher name, (s1,s2,s3) are subjects like math, phy, chem etc..

t_result:
r_id, s_name, t_name, count

where r_id is the primary key, s_name is the student name (from student table), t_name is the teacher name (from teacher table) and count gives count (more on that in a moment)

Now, what I would like to do is as below:

there could be any number of records in student and teacher table, and none as of now in the results table. Now, using mySQL I would like to scan the contents of student table, and for each record in this table, i would like to pickup:

s1 and then compare the value with (s1,s2,s3) columns in teacher table s2 and then compare the value with (s1,s2,s3) columns in teacher table s3 and then compare the value with (s1,s2,s3) columns in teacher table

and the get the number of matching values and store in count.

For more clarity, if for the first record in student table, s1, s2 and s3 are "phy", "chem" and "maths", and if the first record in teacher table is "maths", "phy", "computer", then in this case, student.s1 matches with teacher.s2 - so now count is 1. then student.s2 is matched against the s1,s2,s3 in teacher table, but match is 0, so count is kept 1; again student.s3 is matched against s1,s2,s3 in teacher table,this time it matches with teacher.s1, so count is incremented to 2. So at the end of comparison of first record in student table with first record of teacher table, i get the count=2. Now i would insert a row into the result table, with student name, teacher name, and the so obtained count.

Basically I want to get the number of s1,s2,s3 match between the student table and teacher table, for each row in student table and then put this into the result table.

I know only basic operations in mysql- like selecting inserting deleting etc.. I am assuming that such an operation would require more than that, something like plsql and stored procedure?

FYI, I am using phpmyadmin, and the tables are stored there. I would be using php to fetch the results from the table,and to execute these queries.

Please let me know the approach for this.

Thank You!

Answer 1

Well. Thank you for this challenge. A nice mental exercise :).

Here's the query; just convert it into an insert:

select s_name, t_name, count(*) cnt from
(
select s.s_name, s.s s_s, t.t_name, t.s t_s from
(
select s_id, s_name, s1 s from t_student
union
select s_id, s_name, s2 s from t_student
union
select s_id, s_name, s3 s from t_student
) s
inner join
(
select t_id, t_name, s1 s from t_teacher
union
select t_id, t_name, s2 s from t_teacher
union
select t_id, t_name, s3 s from t_teacher
) t
on t.s = s.s
) m
group by s_name, t_name
;

---

Edit: Actual run:

mysql> select * from t_student;
+------+--------+------+------+------+
| s_id | s_name | s1   | s2   | s3   |
+------+--------+------+------+------+
|    1 | st1    | qqq  | www  | eee  |
|    2 | st2    | 111  | 222  | 333  |
|    3 | st3    | zzz  | xxx  | ccc  |
+------+--------+------+------+------+
3 rows in set (0.00 sec)

---

mysql> select * from t_teacher;
+------+--------+------+------+------+
| t_id | t_name | s1   | s2   | s3   |
+------+--------+------+------+------+
|    1 | te1    | qqq  | www  | eee  |
|    2 | te2    | 111  | 222  | nnn  |
|    3 | te3    | zzz  | nnn  | nnn  |
+------+--------+------+------+------+
3 rows in set (0.00 sec)

---

mysql> select s_name, t_name, count(*) cnt from
    -> (
    -> select s.s_name, s.s s_s, t.t_name, t.s t_s from
    -> (
    -> select s_id, s_name, s1 s from t_student
    -> union
    -> select s_id, s_name, s2 s from t_student
    -> union
    -> select s_id, s_name, s3 s from t_student
    -> ) s
    -> inner join
    -> (
    -> select t_id, t_name, s1 s from t_teacher
    -> union
    -> select t_id, t_name, s2 s from t_teacher
    -> union
    -> select t_id, t_name, s3 s from t_teacher
    -> ) t
    -> on t.s = s.s
    -> ) m
    -> group by s_name, t_name
    -> ;

---

+--------+--------+-----+
| s_name | t_name | cnt |
+--------+--------+-----+
| st1    | te1    |   3 |
| st2    | te2    |   2 |
| st3    | te3    |   1 |
+--------+--------+-----+
3 rows in set (0.00 sec)

Answer 2

Actually, you don't have to need any procedural code to resolve this. The approach is called "normalization". You need more than one table to represent the subjects for a teacher, so that s1, s2, s3 values go to a single column in a separate table, which is in a foreign key relationship with the teacher table. Same for the other relationships. (That's why the term "relational database" in the first place.)

A related discussion is here: DB Design: 1st Normal Form and Repeating Groups.

Edit

Looks like an assignment rather than a production issue :). Anyway, you still may try a convoluted pure-SQL approach to normalize in the query rather than in the schema. Weird but not impossible.

Here's the essence of the trick. Use this as a subquery to get a normalized representation of your data:

select s_id, s_name, s1 s from t_student
union
select s_id, s_name, s2 s from t_student
union
select s_id, s_name, s3 s from t_student

Answer 3

You should modify your tables to

t_student:
s_id, s_name, s_s

and

t_teacher:
t_id, t_name, t_s

and split your s1, s2, s3 to separate rows. Then you can do a simple join between t_student and t_teacher.

Answer 4

Your database design needs some work. Start by normalising your data, moving the repeated subject columns out of the teacher and student tables. You could do this by creating the following tables:

CREATE TABLE t_subjects ( subject_id INT, name VARCHAR(30) );
CREATE TABLE t_teacher_subjects ( teach_id INT, subject_id INT);
CREATE TABLE t_student_subjects ( student_id INT, subject_id INT);

Remove the s1, s2, s3 columns from teacher and student, and you will then be able to populate your results table (provided it has an auto_increment primary key) in a single query - something like this:

insert into t_result (s_name, t_name, count) 
select t_teacher.t_name, t_student.s_name, count(*) as c from
t_teacher_subjects 
    inner join t_student_subjects on t_teacher_subjects.subject_id = t_student_subjects.subject_id
    inner join t_teacher on t_teacher_subjects.teach_id = t_teacher.t_id
    inner join t_student on t_student_subjects.student_id = t_student.s_id
group by t_teacher.t_name, t_student.s_name;