How can i SELECT from all tables at once?

Question

I have 32 tables in mySQL database and i wanna select from them where the table contains a specific value. I want to do something like this:

SELECT * FROM * WHERE DeviceId = '2009240214';

I know there are many tables which contains the DeviceId. instead of join one by one, is there a way i can select all of them at once to get the result where DeviceId = '2009240214' is in the table?

Thank You

Does this answer your question? [Get table names using SELECT statement in MySQL](https://stackoverflow.com/questions/8334493/get-table-names-using-select-statement-in-mysql) — Vokail, Jun 15 '21 at 12:49
Maybe this do `SELECT * FROM , , ,.... WHERE DeviceId = '2009240214';` — Raky, Jun 15 '21 at 12:49
@Cid, Sir but still it's an Idea for a beginner to begin with. — Raky, Jun 15 '21 at 12:51
Not all of them have DeviceId, i know i can select one by one, but i'd like to know if there's a way to select from all of the at once — Arthur, Jun 15 '21 at 12:57
What have you tried so far? Where are you stuck? This looks like horrible database design — Nico Haase, Jun 15 '21 at 15:55

Raky · Answer 1 · 2021-06-15T15:53:58.517

Following SQL statement will list all tables in DB named books with Column_Name device_id

DROP PROCEDURE IF EXISTS books.raky;
DELIMITER ;;
CREATE PROCEDURE books.raky()
BEGIN
    DECLARE done BIGINT DEFAULT 0;
    DECLARE table_name CHAR(255) DEFAULT "";
    DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='books' AND COLUMN_NAME IN ('ISBN10')  ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;
    
    myloop: loop
        fetch cur1 into table_name;
          
        if done then
            leave myloop;
        end if;
        set @sqlraky = CONCAT("select * from `books`.", table_name, " where ISBN10 = '0195153448'");
        SELECT @sqlraky;
        prepare stmt from @sqlraky;
        execute stmt;
        drop prepare stmt;
    end loop;
    close cur1;
END; 
;;
call books.raky;

Please try the above code. Replace books with your database name and ISBN10 with DeviceID. Also, change the value in ISBN10 ='0195153448' with DeviceId = '2009240214';

This is as close as I could Get. And I know there is scope for Improvement.

@Cid, If you may please kindly improve my code – Raky Jun 15 '21 at 15:54 — Raky, Jun 15 '21 at 15:54

How can i SELECT from all tables at once?

1 Answers1