0

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

  • 4
    why separate tables for each location? – Jack Douglas Jun 8 '17 at 16:38
  • Your dbfiddle set-up has "silo_1" and "silo_2" in the loc_list location_table_name column, rather than location_1 and location_2. Is that correct, or a typo? Also: does every location have its own table (meaning a new one is added when new locations are added, and some sort of dynamic SQL is required), or is there a known, static number of locations/silos (in which case the location table names can be safely be hard-coded)? – RDFozz Jun 8 '17 at 21:00
  • I would guess a couple of LEFT JOIN, and no UNIONs would do the trick. – Rick James Jun 9 '17 at 0:10
  • @RDFozz It was a typo. Corrected. Thanks for your support – Abel Jojo Jun 9 '17 at 5:23
  • @JackDouglas I don't have option to edit table structure. Thanks for support – Abel Jojo Jun 9 '17 at 5:24
2

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.

  • Thanks for great support. I got the idea. Create procedure and store it there. Retrieve from temporary table. Thanks again. – Abel Jojo Jun 9 '17 at 5:32
  • Best of luck! Test it properly - – stefan Jun 9 '17 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.