Select Database name from another Database

Question

I have a DB where several Tables are stored like "Item1", "Item2", "Item3" etc. and one Table called "Items".

In "Items" there are all names so you know what sort of values are in the Tables "Item1", "Item2" etc..

Now I like to Select the data from eg. Table "Item2" where all Inside Temperatures are stored. However I like to acces them via the Items table where the ID is stored of the Item Table. See Table Items. The ID for "Temperature Inside" is 2 so I like to get the data from table Item2

So something like this:

SELECT Value AS "Test"
FROM (SELECT concat('Item', ItemId) FROM Items WHERE ItemName = "Temperature Inside")

Table Item2:

time   Value
............
1      58               
2      67              
3      25 
6      1

Table Items:

ItemId    ItemName 
......................
1         "Temperature Outside"
2         "Temperature Inside"              
3         "Power"             
4         "Voltage"

https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join/6188334#6188334 — Labradorcode, Nov 13 '19 at 09:24
but join works only if I know the name of both tables but I don't know it since the name of table I like to use is stored in the "Items" table — Andy, Nov 13 '19 at 10:39

score 0 · Accepted Answer · answered Nov 13 '19 at 12:00

If I understood you correctly, you can use the below query. You have to use dynamic SQL to generate your select query. First get the id from items table and create your select query using concat. Finally using PREPARE and EXECUTE you can get the required output.

select @var :=itemid from items where itemname='Temperature Inside';
set @sqltext := concat('select * from item',@var);
PREPARE stmt FROM @sqltext;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Check DEMO Here

Select Database name from another Database

1 Answers1