加入多个表并创建一个表

I having 3 tables called Advance, Transport and Medicine. I want to join these three tables and come up with a table like last one for emp_id 1. Is it possible to do this in sql level using dql (doctrine) or sql?

我有3张叫做Advance,Transport和Medicine的桌子。我想加入这三个表,并为emp_id 1提供一个像最后一个表的表。是否可以使用dql(doctrine)或sql在sql级别执行此操作?

1 个解决方案

#1

CREATE TABLE NewTable
SELECT  Date, 
        Emp_ID,
        Amount As Advance, 
        NULL AS Transport,
        NULL AS Medicine
FROM    Advance
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        Amount AS Transport,
        NULL AS Medicine
FROM    Transport
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        NULL AS Transport,
        Amount AS Medicine
FROM    Medicine
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID

• CREATE TABLE...SELECT Syntax

CREATE TABLE ... SELECT语法

You can also create a VIEW on this,

你也可以在这上面创建一个VIEW,

CREATE VIEW EmployeeView
AS
SELECT  Date, 
        Emp_ID,
        Amount As Advance, 
        NULL AS Transport,
        NULL AS Medicine
FROM    Advance
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        Amount AS Transport,
        NULL AS Medicine
FROM    Transport
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        NULL AS Transport,
        Amount AS Medicine
FROM    Medicine
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID

• CREATE VIEW Syntax

CREATE VIEW语法