表中数据的SELECT语句依赖于一个字段

Question

有一条sql语句可以从此sqlfiddle返回有价值的信息。

CREATE TABLE `civil`(
      civil_id      INTEGER  NOT NULL PRIMARY KEY,
      name          VARCHAR(25)  ,
      address_civil VARCHAR(40)  NOT NULL ,
      telephone     VARCHAR(20 ) NOT NULL ,
      email         VARCHAR(30)  NOT NULL ,
      comment       VARCHAR(35 )  NOT NULL ,
      CONSTRAINT `uc_Info_civil` UNIQUE (`civil_id`)           
);

CREATE TABLE `worker`(
      worker_id      INTEGER  NOT NULL PRIMARY KEY,
      name           VARCHAR(25)  ,
      address_worker VARCHAR(40)  NOT NULL ,
      departament    VARCHAR(40)  NOT NULL ,
      age            INTEGER  NOT NULL ,      
      CONSTRAINT `uc_Info_civil` UNIQUE (`worker_id`)           
);

CREATE TABLE `event`(
      id_event      INTEGER  NOT NULL PRIMARY KEY,
      type_event    VARCHAR(25)  ,
      personID        INT  NOT NULL ,
      date          DATE NOT NULL ,   
      isWorker INT NOT NULL,
      CONSTRAINT `uc_Info_event` UNIQUE (`id_event`)           
);

ALTER TABLE `event` ADD INDEX (personID);

INSERT INTO `civil` VALUES (  1, 'JOE','SOME ADDRESS', '5646464','joe@gms.com','no comment' );
INSERT INTO `civil` VALUES (  2, 'MOE','SOME ADDRESS', '6776543','froe@ges.com','no comment' );

INSERT INTO `worker` VALUES (  1, 'MARGE','STATES W54', 'finances',34);
INSERT INTO `worker` VALUES (  2, 'SASHA','LIVER S 34', 'sells',23 );

INSERT INTO `event` VALUES (1,"type1", 1, '12-12-12',1);

正在做什么

SELECT  a.*,
        IF(a.Isworker = 1,c.name, b.name) AS personname
FROM    event a
        LEFT JOIN civil b
            ON a.personid = b.civil_id
        LEFT JOIN worker c
            ON a.personid = c.worker_ID

返回

ID_EVENT TYPE_EVENT PERSONID DATE                   ISWORKER PERSONNAME
-----------------------------------------------------------------------------
1   type1   1   December, 12 2012 00:00:00+0000        1    MARGE

如何根据字段isworker获取civil和worker表的所有数据(而不仅仅是名称)？

Answer 1

我可能会建议您重新构建/更好地规范化您的表...但只要使用您已有的内容，一种选择是将isworker检查添加到join语句中。除此之外，您必须在civilian和worker之间找到可比较的字段，以便在IF()语句中相互抵消，就像您对personname所使用的一样。

SELECT  *
FROM    event a
        LEFT JOIN civil b
            ON a.personid = b.civil_id
            AND a.Isworker != 1
        LEFT JOIN worker c
            ON a.personid = c.worker_ID
            AND a.Isworker = 1