CODEWITHSUNDEEP
mysql
ywan
ywan

Reputation: 137

How to print the tables' name which don't contain a ID or don't contain the id column?

I am new to MySQL. Now, I have 40 tables. A part of they contains a field named "case_id" (char). I want to find which tables have the "case_id" column but don't have case_id = "123".

I just select all tables having the "case_id" column but I don't know how to find the required table from it.

Do you have any suggestions? Thank you so much!

select DISTINCT TABLE_NAME as test_table from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME IN ("case_id") and TABLE_SCHEMA="test_db";

For example,

Table A has a record with "case_id" = "123". so, it would not be printed. Table B doesn't contain the column "case_id, it would not be printed either. Table C has "case_id" but there isn't a record with "case_id" = "123", the name "Table C" would be printed.

EDIT #2

Just update my code based on @abk 's answer. I just changed the name of the scheme. 

CREATE DEFINER=`root`@`localhost` PROCEDURE `MISS_CASE_CHECK`()
BEGIN
    DECLARE Table_name TEXT;
    DECLARE done INT DEFAULT 0;
    DECLARE str  LONGTEXT;
    DECLARE my_cursor CURSOR FOR
    SELECT DISTINCT TABLE_NAME
    From INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ("case_id") and TABLE_SCHEMA="wes_bk";

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN my_cursor;
   SET str = '';      
    WHILE done = 0 DO
        BEGIN
          FETCH my_cursor INTO Table_name;
          SET str = CONCAT(str,Table_name,',');  
        END;
    END WHILE;
    SELECT LEFT(str, LENGTH(str) - 1);
    CLOSE my_cursor;
    SELECT str;
END

Unfortunately, I received many error messages:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE done INT DEFAULT 0' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE str  LONGTEXT' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE my_cursor CURSOR FOR
    SELECT DISTINCT TABLE_NAME
    From INFORMATION' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPEN my_cursor' at line 1
ERROR 1193 (HY000): Unknown system variable 'str'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE done = 0 DO
        BEGIN
          FETCH my_cursor INTO Table_name' at line 1
ERROR 1193 (HY000): Unknown system variable 'str'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END WHILE' at line 1
ERROR 1054 (42S22): Unknown column 'str' in 'field list'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLOSE my_cursor' at line 1
ERROR 1054 (42S22): Unknown column 'str' in 'field list'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

My mysql version is 5.7.12. Any suggestions?

Upvotes: 0

Views: 121

Answers (2)

nbk
nbk

Reputation: 49373

The stored procedure must look more like this.

The table_name will be put o a text variable and you see the result as comma-separated text.

If you need a table with rows, you must add a temporary table and select this at the end.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `MISS_CASE_CHECK`()
BEGIN
    DECLARE Tablename TEXT;
    DECLARE done INT DEFAULT 0;
    DECLARE str  LONGTEXT;
    DECLARE my_cursor CURSOR FOR
    SELECT DISTINCT TABLE_NAME
    From INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME ='Id' and TABLE_SCHEMA='testdb';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET @tablen = '';
    OPEN my_cursor;
   SET str = '';      
    WHILE done = 0 DO
        BEGIN
          FETCH my_cursor INTO Tablename;
          SET str = CONCAT(str,Tablename,',');  
        END;
    END WHILE;
    SELECT LEFT(str, LENGTH(str) - 1);
    CLOSE my_cursor;
END//
DELIMITER ;

Upvotes: 1

JNevill
JNevill

Reputation: 50019

You won't be able to do this in a single step (unless there is some super backwards creative way). Instead you will do your existing query to get a list of tables that are candidates for your second criteria. Then you will issue a single SQL statement for each table in that list to test if it has your value.

You can speed this up by having your first SQL statement write your other SQL statements as one big UNION QUERY that you can then execute as a second step.

Something like:

select DISTINCT 
    CONCAT('SELECT DISTINCT ', TABLE_NAME, ' AS table_name FROM ', TABLE_NAME, ' WHERE NOT EXISTS (SELECT 1 FROM ', TABLE_NAME, ' WHERE case_id=123) UNION ALL ') as sqlstatement 
from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME IN ("case_id") and TABLE_SCHEMA="test_db";

Just take that output, trim off the last UNION ALL and submit it (I haven't tested, but theoretically this should do the job)

If you will be doing this often, then this should be moved to a stored procedure or some other scripting language where you loop through your candidates (from your original SQL) and then issue the sql to query that table to see if it has that case_id and add it to a new table/list/array/something for output.

Upvotes: 0

Related Questions