Select table records from another table

user_16134710 0
Viewed 40

I have a requirement to get all the records from a table but the condition is I want to get the table name from another table. Any suggestions on how I can do it?

SELECT *
  FROM (SELECT table_name
          FROM table_containing_table_names
         WHERE table_id = 17) AS Records;
mysql sql
1 Answers

You'll need dynamic sql for such things.

An example

create table foo (
id int auto_increment primary key, 
foo int
);
insert into foo (foo) values (21);

create table bar (
id int auto_increment primary key, 
bar int
);
insert into bar (bar) values (42);

create table table_containing_table_names (
table_id int primary key, 
table_name varchar(30)
);

insert into table_containing_table_names
(table_id, table_name) values 
(21, 'foo'), (42,'bar');

CREATE PROCEDURE getDataFromTableId (IN id INT)
BEGIN
  SET @table_id = id;
  SET @table_name = (
            select table_name 
            from table_containing_table_names 
            where table_id = @table_id);
  
  IF @table_name IS NOT NULL
  THEN
    SET @stmt = CONCAT('select * from `', @table_name, '`;');
  
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END;

call getDataFromTableId(21);

id | foo
-: | --:
 1 |  21

✓

call getDataFromTableId(42);

id | bar
-: | --:
 1 |  42

✓

call getDataFromTableId(888);

✓

db<>fiddle here

user_40034190
Related