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

You CANNOT do this [left join (concat('tbl', '_', t.tbl_type))] in SQL unless you create dynamic SQL. It probably makes more sense to construct the query string in PHP. — Paul Maxwell
– Paul Maxwell, Commented Mar 13, 2015 at 12:39
different case, different response. there should be a comment filter :D — Herman Nz
– Herman Nz, Commented Mar 13, 2015 at 23:21

Jim Mc · Accepted Answer · 2015-03-13 23:13:21Z

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.

answered Mar 13, 2015 at 23:13

Jim Mc

3461 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query join with table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related