Mysql – Select one row from n number table; Join / Union table with looping

join;MySQLsubquery

I have a list table.

locations_list

id |  location | table
----------------------
1  | Location 1 | location_1
2  | Location 2 | location_2    
3  | Location 3 | location_3

location_1

delivered_time       |  item_name 
-----------------------------------------------
2017-06-08 16:37:57  | Bag 1
2017-06-08 19:37:57  | Bag 2

location_2

delivered_time       |  item_name 
-----------------------------------------------
2017-06-08 15:37:57  | Bag 3
2017-06-08 20:37:57  | Bag 4

output expected

 id |  location | table       | delivered_time       |  item_name 
 ------------------------------------------------------------------
 1  | Location 1 | location_1 | 2017-06-08 16:37:57  | Bag 2
 2  | Location 2 | location_2 | 2017-06-08 20:37:57  | Bag 4  
 3  | Location 3 | location_3 |                      |

The join table names are in first table. I don't have option to put all delivery info into one table and do a where clause.

I tried UNION . But I don't know the table names. It is available in location table only.

How can I solve this?
dbfiddle link

Best Answer

Using the following test data, you may find the procedure underneath useful. It will need some adjustments. However, it may give you a starting point (MySQL 5.7).

Test data:

CREATE TABLE `loc_list` (
  `id` int(11) NOT NULL,
  `location_name` varchar(100) NOT NULL,
  `location_table_name` varchar(100) NOT NULL
);

CREATE TABLE `location_1` (
  `delivered_time` timestamp NOT NULL,
  `item` varchar(100) NOT NULL
);

CREATE TABLE `location_2` (
  `delivered_time` timestamp NOT NULL,
  `item` varchar(100) NOT NULL
);

INSERT INTO `location_1` (`delivered_time`, `item`) VALUES
('2017-06-08 14:07:57', 'Bag 1'), ('2017-06-08 14:08:57', 'Bag 2');

INSERT INTO `location_2` (`delivered_time`, `item`) VALUES
('2017-06-08 14:06:57', 'Bag 3'), ('2017-06-08 14:09:57', 'Bag 4');

INSERT INTO `loc_list` (`id`, `location_name`,`location_table_name`) VALUES
(1, 'Location 1', 'location_1'), (2, 'Location 2', 'location_2');

Table for storing the procedure's output:

create table output_(
  id_ integer
, location_ varchar(100)
, table_ varchar(100)
, delivered_time timestamp
, item_name varchar(100)
);

Draft version of the procedure:

--drop procedure findloc;

delimiter //

create procedure findloc()
begin
  declare t_name varchar(100) default '';
  DECLARE done TINYINT DEFAULT 0;
  declare cur_ cursor for select location_table_name from loc_list;
  declare continue handler for not found set done = 1 ;

  open cur_;
  read_loop: LOOP
    fetch from cur_ into t_name;
    if done then
      leave read_loop; 
    end if;
    select @t := t_name ;  
    set @qry = concat (' 
      insert into output_ 
      (id_, location_, table_, delivered_time, item_name)
      select
        id, location_name, location_table_name,
        delivered_time, item 
      from loc_list L, (select * from ', t_name, ') T 
      where L.location_table_name = @t 
    ') ;
    prepare stmt from @qry ;
    execute stmt ;
    deallocate prepare stmt ;
  end loop;
  close cur_;
end//

delimiter ;


-- clear the table - just in case
delete from output_;
-- execute the procedure
call findloc();

Output (just select from the output_ table):

mysql> select * from output_;
+------+------------+------------+---------------------+-----------+
| id_  | location_  | table_     | delivered_time      | item_name |
+------+------------+------------+---------------------+-----------+
|    1 | Location 1 | location_1 | 2017-06-08 14:07:57 | Bag 1     |
|    1 | Location 1 | location_1 | 2017-06-08 14:08:57 | Bag 2     |
|    2 | Location 2 | location_2 | 2017-06-08 14:06:57 | Bag 3     |
|    2 | Location 2 | location_2 | 2017-06-08 14:09:57 | Bag 4     |
+------+------------+------------+---------------------+-----------+

This may look a bit different from what you'd expect. However, in your dbfiddle, you had (in the loc_list): location_table_name 'silo_1' and 'silo_2', whereas the timestamps and bags were INSERTed into the tables called 'location_1' and 'location_2', as @RDFozz has pointed out.

Related Question