How to combine 4 or more tables with different joins, avoiding some minor duplication

Question

I'm trying to build a query for combining 4 of my tables into a pre-created table, my query was running smoothly for the first 3 tables. Here is my query (*query and tables are already simplified)

INSERT INTO goaltable 
SELECT  b.employeeID, b.employeeName, a.grupID,  
        a.shiftID, b.scheduleDate, b.scheduleWorkTime,  
        c.hierarchyID, c.companyID, c.city1, c.phone,
        d.machineID, d.tapDate, e.tapTime, e.tapID 

    FROM mstrattendance AS b LEFT JOIN workgrup AS a 
        ON b.employeeID = a.employeeID 
            AND b.scheduleDate = a.scheduleDate 

        LEFT JOIN mstremployee as c 
            ON b.employeeID = c.employeeID 

        --working query limit

        LEFT JOIN rawfpmachine as d 
            ON b.employeeID = d.employeeID AND b.scheduleDate = d.tapDate 

        RIGHT JOIN rawfpmachine as e 
            ON b.employeeID = c.employeeID AND b.scheduleDate = d.tapDate;

I already tried different query for the last part of code, but it keeps returning null values, or making the whole tables null.

Here is my tables

CREATE TABLE "mstrattendance" ("employeeID" varchar(16),"employeeName" varchar(64),"scheduleDate" varchar(10),"scheduleWorkTime" varchar(5));

INSERT INTO mstrattendance ("employeeID","employeeName","scheduleDate","scheduleWorkTime")
VALUES
('193011','Asuk','13-08-2016','07:30'),
('193011','Asuk','14-08-2016','07:00'),
('193011','Asuk','15-08-2016','99:99'),
('193015','Bara','13-08-2016','07:30'),
('193015','Bara','14-08-2016','07:00'),
('193015','Bara','15-08-2016','99:99'),
('193015','Anna','13-08-2016','99:99'),
('124012','Anna','14-08-2016','07:30'),
('124012','Anna','15-08-2016','07:00');

CREATE TABLE "workgrup" ("employeeID" varchar(16),"shiftID" varchar(4),"scheduleDate" varchar(10),"grupID" varchar(3));

INSERT INTO workgrup ("employeeID","shiftID","scheduleDate","grupID")
VALUES
('193011','1P1','13-08-2016','PO'),
('193011','1P1','14-08-2016','PO'),
('193011','1P1','15-08-2016','PO'),
('193015','1P1','13-08-2016','PO'),
('193015','1P1','14-08-2016','PO'),
('124012','1P2','14-08-2016','FA');

CREATE TABLE "mstremployee" ("employeeID" varchar(16),"companyID" varchar(4),"hierarchyID" varchar(10),"city1" varchar(32),"phone" varchar (16));

INSERT INTO mstremployee ("employeeID","companyID","hierarchyID","city1","phone")
VALUES
('193011','ALU','1001021','Kartaja','0832851'),
('193015','ALU','1001021','Kartaja','0832117'),
('124012','GLA','1002003','Angertang','0811345');

CREATE TABLE "rawfpmachine" ("employeeID" varchar(16),"machineID" varchar(4),"tapDate" varchar(10),"tapTime" varchar(5),"tapID" varchar (2));

INSERT INTO rawfpmachine ("employeeID","machineID","tapDate","tapTime","tapID")
VALUES
('193011','001','13-08-2016','07:50','00'),
('193011','002','13-08-2016','12:02','02'),
('193011','002','13-08-2016','12:56','03'),
('193011','001','13-08-2016','16:40','01'),
('193015','001','13-08-2016','07:58','00'),
('193015','002','13-08-2016','12:01','02'),
('193015','002','13-08-2016','12:58','03'),
('193015','001','13-08-2016','16:33','01'),
('124012','003','14-08-2016','07:54','00'),
('124012','004','14-08-2016','12:03','02'),
('124012','004','14-08-2016','12:56','03'),
('124012','003','14-08-2016','16:38','01'),
('193011','001','14-08-2016','07:58','00'),
('193011','001','14-08-2016','16:01','01');

my goal is to create table like this

CREATE TABLE "goaltable" ("employeeID" varchar(16),"employeeName" varchar(64),"scheduleDate" varchar(10),"scheduleWorkTime" varchar(5),"shiftID" varchar(4),"groupID" varchar(3)),"companyID" varchar(4),"hierarchyID" varchar(10),"city1" varchar(32),"phone" varchar (16),"machineID" varchar(4),"tapDate" varchar(10),"tapTime" varchar(5),"tapID" varchar (2));

INSERT INTO goaltable ("employeeID","employeeName","scheduleDate","scheduleWorkTime","shiftID","grupID","companyID","hierarchyID","city1","phone","machineID","tapDate","tapTime","tapID")
VALUES
('193011','Asuk','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832851','001','13-08-2016','07:50','00'),
('193011','Asuk','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832851','002','13-08-2016','12:02','02'),
('193011','Asuk','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832851','002','13-08-2016','12:56','03'),
('193011','Asuk','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832851','001','13-08-2016','16:40','01'),
('193015','Bara','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832117','001','13-08-2016','07:58','00'),
('193015','Bara','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832117','002','13-08-2016','12:01','02'),
('193015','Bara','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832117','002','13-08-2016','12:58','03'),
('193015','Bara','13-08-2016','07:30','1P1','PO','ALU','1001021','Kartaja','0832117','001','13-08-2016','16:33','01'),
('124012','Anna','14-08-2016','07:30','1P2',,'FA','GLA','1002003','Angertang','0811345','003','14-08-2016','07:54','00'),
('124012','Anna','14-08-2016','07:30','1P2','FA','GLA','1002003','Angertang','0811345','004','14-08-2016','12:03','02'),
('124012','Anna','14-08-2016','07:30','1P2','FA','GLA','1002003','Angertang','0811345','004','14-08-2016','12:56','03'),
('124012','Anna','14-08-2016','07:30','1P2','FA','GLA','1002003','Angertang','0811345','003','14-08-2016','16:38','01'),
('193011','Bara','14-08-2016','07:00','1P1','PO','ALU','1001021','Kartaja','0832117','001','14-08-2016','07:58','00'),
('193011','Bara','14-08-2016','07:00','1P1','PO','ALU','1001021','Kartaja','0832117','001','14-08-2016','16:01','01');

any help is much appreciated, thank you in advance.

*UPDATE here is my newest and closest to goal query

INSERT INTO goaltable 
SELECT  b.employeeID, b.employeeName, b.scheduleDate, b.scheduleWorkTime, a.grupID,  
    a.shiftID, c.companyID, c.hierarchyID, c.city1, c.phone,
    d.machineID, d.tapDate, d.tapTime, d.tapID 

FROM mstrattendance AS b LEFT JOIN workgrup AS a 
    ON b.employeeID = a.employeeID 
        AND b.scheduleDate = a.scheduleDate 

    LEFT JOIN mstremployee as c 
        ON b.employeeID = c.employeeID 

    INNER JOIN rawfpmachine as d 
        ON b.employeeID = d.employeeID AND b.scheduleDate = d.tapDate

--this query is worked

but I still not same as my goal table, because there is still some duplicate in there, just when there are a day off.

goal table wannabe

and... I can't post image.. As you can see, there is a duplication every time scheduleDate value 99:99 the rest rows is great though (or I didn't notice)

and I'm not supposed to use WHERE scheduleWorkTime <> '99:99';

"t keeps returning null values, or making the whole tables null": please expand. One uses outer joins when you want to include rows where there isn't a matching row on the otherside. Given both left and right outer joins I would expect to see a lot of nulls. — Richard, Oct 17 '16 at 09:32
You have a problem on the right join. You set `e` as the alias of `rawfpmachine ` however you don't use `e` anywhere on the join part — Rumpelstinsk, Oct 17 '16 at 09:35
I've try to use `d` only instead `d` and `e`, then my memory is overloaded, idk why, it returns a lot of rows. and sorry if my english is not good enough @Rumpelstinsk — Joe Adventa, Oct 17 '16 at 09:41
I dont understand what do you mean by expand?, that later query (after safe limit) is my last try attempt before asking here in SO, I've try using other joins too, as i see fit (in my logic at least). Is there any way to solve this without using joins? or simpler joins? @Richard — Joe Adventa, Oct 17 '16 at 09:45
Expand: put in more details. Maybe an example of the data you are getting alongside an example of what you expect. Also reduce your code (fewer columns…) to a minimal re-create. — Richard, Oct 17 '16 at 10:22
sorry for very late reply @Richard, okay, i'll be adding the data I getting shortly, but the code I afraid it already being reduced a lot. I only adding minimum data for each table for people to see the example of columns from each table to be added into the "goal" table. You can see that each row is very different than others. Thanks for your explanation by the way. — Joe Adventa, Oct 18 '16 at 07:18

How to combine 4 or more tables with different joins, avoiding some minor duplication

0 Answers0