如何将值与列名相同的多个表中的列值进行比较？

Question

I have five different tables with same column names in the database. I want to check whether a string value matches with the column value of any of the five table.

For example: I have database that contains exam results of 8th standard. Five different table contains marks of each student. The first table contains marks of class 8th section A. The second table contains marks of class 8th section B. The Third table contains marks of class 8th section C. (So on)

All the table has same column names. (Roll no, math marks, history marks, Geography marks and Social study marks)

I want to fetch who have secured more than 90 in math. I want to know the single query of mysqli.

Thanks in advance.

Answer 1

Assuming that the same students do not appear multiple tables, you should be able to take a union of the five tables (but see below for some comments on your design):

SELECT *
FROM table1
WHERE math_marks > 90
UNION ALL
SELECT *
FROM table2
WHERE math_marks > 90
UNION ALL
SELECT *
FROM table3
WHERE math_marks > 90
UNION ALL
SELECT *
FROM table4
WHERE math_marks > 90
UNION ALL
SELECT *
FROM table5
WHERE math_marks > 90;

A better design here would be to have a single table containing all student marks. You could have a column for the student ID, section, and class. This would make querying and keeping track of things easier IMO.