Get the table name(s) from MySQL database where column name is like X value is equal to Y

Question

I want to know if there is a way with MySQL request to get the table names in which a column value is equal to a particular one.

For example I have 3 tables :

Classroom1             Classroom2              Classroom3
+------+                +------+                 +------+
| name |                | name |                 | name |
+------+                +------+                 +------+
| Bob  |                | Dan  |                 | Mike |
+------+                +------+                 +------+
| Mark |                | Noa  |                 | Bob  |
+------+                +------+                 +------+

Let's say I want to get the classroom in which there is a student whose name is 'Bob'. I want to get, as response

['Classroom1','Classroom3']

Is there anything like that in MySQL ?

Thanks in advance !!

EDIT

Sorry, I might have used a wrong word. What I meant was that I want to know if there is a way with MySQL request to get the table names in which a cell value is equal to a particular one.

@KeyurShah **stop** confusing people... you mean mysql != sql-server — B001ᛦ, Nov 24 '17 at 09:59
Possible duplicate of [How to find all the tables in MySQL with specific column names in them?](https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them) — l.g.karolos, Nov 24 '17 at 10:05
@B001 Mysql is owned by Oracle and SQL is owned by Microsoft. Secondly SQL is Structured Query Language and MySQL is a relational database management system. — Keyur Shah, Nov 24 '17 at 10:07

l.g.karolos · Answer 1 · 2017-11-24T10:17:09.680

1

Check this question

SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('COLUMN_TO_FIND')
        AND TABLE_SCHEMA='YOUR_SCHEMA';

You can also use normal functions like (Distinct,Count,etc). Cheers!

edited Nov 24 '17 at 10:17

answered Nov 24 '17 at 09:54

l.g.karolos

1,131
1
10
25

Sorry didn't search for an answer – l.g.karolos Nov 24 '17 at 10:04
Looked like you have just replaced columnA,columnB with COLLUMN_TO_FIND – Keyur Shah Nov 24 '17 at 10:08
No much things to do :D .Just a select for finding tables I made and removed my tables and my schema – l.g.karolos Nov 24 '17 at 10:12
1

lol :D I know it is a simple query! I will take my words back. Anyways you have misspelled column inside IN. – Keyur Shah Nov 24 '17 at 10:13
Thank you but this request return me all tables which have a column which name is 'name'. But what I need is the list of table where colum name is 'name' and value is equal to 'Bob'. – Megaman Nov 24 '17 at 10:26

score 1 · Answer 2 · answered Nov 24 '17 at 11:12

I am adding another answer if someone needs the first one

So first you have to find the table match the column you want.

SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('COLUMN_TO_FIND')
        AND TABLE_SCHEMA='YOUR_SCHEMA';

Now, you want to search each table with the specific column for a specific value inside.

You need to create a procedure iterating over these tables and selecting by your clause

DROP PROCEDURE IF EXISTS findName;

DELIMITER //

CREATE PROCEDURE findName()
BEGIN
  DECLARE _tablename VARCHAR(255);
  DECLARE _columnname VARCHAR(255);
  DECLARE _columnNameToFind VARCHAR(255) DEFAULT 'bob';

  DECLARE cur1 CURSOR FOR SELECT 
                             CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
                            COLUMN_NAME AS column_name 
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE COLUMN_NAME IN ('YOUR_COLUMN_NAME')
                                AND TABLE_SCHEMA='YOUR_TABLE';

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO _tablename, _columnname;

    SET @s = CONCAT('SELECT * FROM ', _tablename, ' WHERE ', _columnname =_columnNameToFind);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE cur1;
END//

DELIMITER ;

CALL findName();

See how to create a procedure here

See how to loop inside a procedure here

See how the cursors in mysql works here

score 0 · Accepted Answer · answered Nov 28 '17 at 14:18

Thanks to l.g.karolos I was able to get what I wanted. Here is the code :

DELIMITER $$


DROP PROCEDURE IF EXISTS getStudentFromClassroom$$
CREATE PROCEDURE getStudentFromClassroom(student_name VARCHAR(255))
BEGIN
  DECLARE done INT DEFAULT FALSE;

  DECLARE _tablename VARCHAR(255);
  DECLARE _columnname VARCHAR(255) DEFAULT 'device';
  DECLARE _columnNameToFind VARCHAR(255) ;


   DECLARE cur1 CURSOR FOR SELECT 
                             CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
                            COLUMN_NAME AS column_name 
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE COLUMN_NAME IN ('YOUR_COLUMN_NAME')
                                AND TABLE_SCHEMA='YOUR_TABLE';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO _tablename;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @s = CONCAT('SELECT @student_number := COUNT(DISTINCT ', _columnname,') ',_tablename,' FROM ', _tablename, ' WHERE ', _columnname,' = \'', student_name,'\'');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @s = CONCAT('SELECT @ total_student_number := COUNT(DISTINCT ', _columnname,') ',_tablename,' FROM ', _tablename);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    INSERT INTO getStudentResults VALUES (_tablename,@student_number,@total_student_number);

  END LOOP;

  CLOSE cur1;

END$$

With getStudentResults declares as

    CREATE TEMPORARY TABLE IF NOT EXISTS getStudentResults (
   classroom_name VARCHAR(255),
   student_number INT DEFAULT 0,
   total_student_number INT DEFAULT 0
 );

Get the table name(s) from MySQL database where column name is like X value is equal to Y

3 Answers3