Use result of SELECT as table name for another SELECT

Question

Use result of SELECT as table name for another SELECT

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a table of the following style

Father

|----|------- |-------|
| id | precio | tabla |
|----|------- |-------|
|1   | 50     | sxt   |
|----|------- |-------|
|2   | 45     | ext   |
|----|------- |-------|

then I have other tables

|----------------|
| detallessxt    |
|----------------|
| movimientosxt  |
|----------------|
| detallesext    |
|----------------|
| movimientosext |
|----------------|

Currently from the webservice I make a query to father and store the table variable and then I do a SELECT on the corresponding details and movements, concatenating the name of the table.

"SELECT * FROM Detalles"+tabla+" WHERE ID=1"
"SELECT * FROM Movimientos"+tabla+" WHERE ID=1"

but I'm looking for a way to do this with a stored procedure

but I do not find how to nest the selects

to get for example

CALL obtenertodo(id);

and that I return

|-------------------|
| id                |
|-------------------|
| precio            |
|-------------------|
| detalles_attr1    |
|-------------------|  
| movimientos_attr2 |
|-------------------|

I do not have access to the structure of the database, it is designed in a way and I only have permissions to use SELECT , generate functions and stored procedures

mysql sql stored-procedure

asked by Jorge Arturo Juarez 29.08.2017 в 00:29

source

2 answers

MYSQL - Show in 2 fields a query to a single field Load a JList with data from an array

score 1 · Answer 1

This is a special situation where you do not have access to make changes to the structure of the database, in general it should not be done and if you find a database with a similar structure first warn the administrator of the database that is a bad practice.

Now this is only a "solution" if all you want is to consume the database.

a prepared statement is used, in order to execute a statement from a String

BEGIN
    SELECT tabla FROM padre 
    WHERE id= 1
    INTO @tabla;

    SET @query = CONCAT('SELECT * FROM padre as p
                        INNER JOIN detalles', @tabla, 'AS d ON d.id=p.id
                        INNER JOIN Movimientos', @tabla, 'AS m ON m.id=p.id
                        WHERE p.id=1');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
END

score 0 · Answer 2

I think what you need is to make use of inner join or left join, according to how you indicate in your schema your child tables should have the id of your parent table to have a one to many relationship with which you can do something similar to:

select
   Padre.*,
   Hijo1.*,
   Hijo2.*,
   ...
   HijoN
from
     Padre
inner join
     Hijo1
on
     Padre.Id == Hijo1.PadreId
inner join
     Hijo2
on
     Padre.Id = Hijo2.PadreId
....
etc