Mysql query join with table column

Question

I have a scenario where i have one main table. Main table has 2 extra columns one is for table name (child table name) and other is for table id (child table id). when we enter the value in main table we also tell enter value in child table and then we enter the name of the table in main table name field and child id in the child field of the main table.

now when i query i need to join query with child table in a way that i picks up the table name from the column and join query with that table with concat function and then join on child id.

below is the structure of the table and also there values

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tbl_type` enum('multi','gift','pledge') DEFAULT NULL,
  `tbl_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `tbl` (`id`, `timestamp`, `tbl_type`, `tbl_type_id`) VALUES
(1, '2015-03-09 09:39:42', '', 1),
(2, '2015-03-09 22:43:23', 'multi', 2),
(3, '2015-03-09 23:26:38', 'gift', 1),
(4, '2015-03-10 09:46:15', 'pledge', 2);

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_gift` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `tbl_gift` (`id`, `amount`) VALUES
(1, '1231200'),
(2, '1231200');

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_multi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` float(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_pledge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `tbl_pledge` (`id`, `amount`) VALUES
(1, '10000'),
(2, '10200');

so this is simple hard code query

select * from tbl t left join tbl_gift g on g.id = t.tbl_type_id 

but i want to make it dynamic i tried this

select * from tbl t left join (concat('tbl', '_', t.tbl_type)) g on g.id = t.tbl_type_id 

should get the table which i need

(concat('tbl', '_', t.tbl_type)) 

but it get error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('tbl', '_', t.tbl_type)) g on g.id = t.tbl_type_id LIMIT 0, 30' at line 1

Answer 1

The comments by Ankit and Usedby answered your question. SQL does not allow you to provide dynamically constructed table names as you attempted. They provided you with two options: 1) Construct your query dynamically on the PHP side, then SQL see only the static table names or 2) Use the SQL PREPARE command to construct the dynamic table name and the EXECUTE SQL command to execute it.