如何获取在另一个表中定义的列值 [英] How to get column value which define in another table

问题描述

有人帮我追踪预期的输出吗?我还添加了数据库结构

Any one help me for following expected output? I have also added database structure

TABLE1

TABLE1

ID  Work Id Question                     Column  Column Type
1   1   What is your name?       field1   String
2   1   is you have mobile number?   field2   boolean
3   2   is you have passport?        field2   boolean
4   2   are you indian?              field4   boolean
5   2   abc?                         field5   Number
6   3   cde?                         field2   boolean

TABLE2

TABLE2

Id  WorkId  field1  field2  field3  field4  field5
1   1   JOHN      1         
2   2         1       0   1
3   3         0         

预期产量

Expected Output

Work Id Question                   Answer
1   What is your name?          JOHN
1   is you have mobile number?  1
2   is you have passport?           1
2   are you indian?                 0
2   abc?                            1
3   cde?                            0

表结构

Table Structure

CREATE DATABASE /*!32312 IF NOT EXISTS*/`testtest` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `testtest`;

/*Table structure for table `table1` */

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `WorkId` int(11) DEFAULT NULL,
  `Question` varchar(100) DEFAULT NULL,
  `ColumnRef` varchar(100) DEFAULT NULL,
  `ColumnType` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `table1` */

insert  into `table1`(`Id`,`WorkId`,`Question`,`ColumnRef`,`ColumnType`) values (1,1,'What is your name?','field1','String'),(2,1,'is you have mobile number?','field2','boolean'),(3,2,'is you have passport?','field2','boolean'),(4,2,'are you indian?','field4','boolean'),(5,2,'abc?','field5','Number'),(6,3,'CDE?','field2','boolean');

/*Table structure for table `table2` */

DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workid` int(11) DEFAULT NULL,
  `field1` varchar(11) DEFAULT NULL,
  `field2` int(11) DEFAULT NULL,
  `field3` int(11) DEFAULT NULL,
  `field4` int(11) DEFAULT NULL,
  `field5` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `table2` */

insert  into `table2`(`id`,`workid`,`field1`,`field2`,`field3`,`field4`,`field5`) values (1,1,'JOHN',1,NULL,NULL,NULL),(2,2,NULL,1,NULL,0,1),(3,3,NULL,1,NULL,NULL,NULL);

推荐答案

希望这对您有帮助

SELECT t1.workid AS 'workid',t1.Question AS 'Question',
COALESCE(`field1`,`field2`,`field3`,`field4`,`field5`) AS 'Answer'
FROM table1 AS t1
JOIN table2 AS t2 ON t1.workid = t2.workid

SQL提琴

这篇关于如何获取在另一个表中定义的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！