I have a table which has following data in TABLE1

I wanted to make sure all the name have all "work type" available for all those dates only which is in the data. Output should like TABLE2

I tried to put a query like

select t1.name,t1.date,mt.work_type,t1.minutes 
from table1 t1
right join (select disticnt work_type from t1) mt on t1.work_type=mt.work_type

however it didn't work. Please help

CodePudding user response：

You need to join with a subquery that cross joins all the names, dates and work types.

SELECT mt.name, mt.date, mt.work_type, COALESCE(t1.minutes, 0) AS minutes
FROM (
    SELECT DISTINCT t1.name, t1.date, t2.work_type
    FROM table1 AS t1
    CROSS JOIN table1 AS t2
) AS mt
LEFT JOIN table1 AS t1 
    ON t1.name = mt.name AND t1.date = mt.date AND t1.work_type = mt.work_type
ORDER BY mt.name, mt.date, mt.work_type

Note that in the subquery you have to specify the actual table name, not the alias from the main query.

DEMO

CodePudding user response：

In Oracle, you can use a partitioned outer join:

SELECT t.name,
       t."DATE",
       w.work_type,
       COALESCE(t.minutes, 0) AS minutes
FROM   (SELECT DISTINCT work_type FROM table1) w
       LEFT OUTER JOIN table1 t
       PARTITION BY (t.name, t."DATE")
       ON (w.work_type = t.work_type)

Which, for the sample data:

CREATE TABLE table1 (name, "DATE", work_type, minutes) AS
SELECT 'a', DATE '2011-01-01', 'labor',   53 FROM DUAL UNION ALL
SELECT 'a', DATE '2011-01-01', 'private', 58 FROM DUAL UNION ALL
SELECT 'a', DATE '2011-01-01', 'other',   19 FROM DUAL UNION ALL
SELECT 'b', DATE '2011-01-02', 'labor',   31 FROM DUAL UNION ALL
SELECT 'b', DATE '2011-01-02', 'other',   24 FROM DUAL UNION ALL
SELECT 'b', DATE '2011-01-01', 'private', 19 FROM DUAL UNION ALL
SELECT 'c', DATE '2011-01-03', 'labor',   25 FROM DUAL UNION ALL
SELECT 'c', DATE '2011-01-03', 'private', 50 FROM DUAL UNION ALL
SELECT 'c', DATE '2011-01-01', 'private', 23 FROM DUAL UNION ALL
SELECT 'd', DATE '2011-01-01', 'other',   20 FROM DUAL;

Outputs:

NAME	DATE	WORK_TYPE	MINUTES
a	01-JAN-11	labor	53
a	01-JAN-11	other	19
a	01-JAN-11	private	58
b	01-JAN-11	labor	0
b	01-JAN-11	other	0
b	01-JAN-11	private	19
b	02-JAN-11	labor	31
b	02-JAN-11	other	24
b	02-JAN-11	private	0
c	01-JAN-11	labor	0
c	01-JAN-11	other	0
c	01-JAN-11	private	23
c	03-JAN-11	labor	25
c	03-JAN-11	other	0
c	03-JAN-11	private	50
d	01-JAN-11	labor	0
d	01-JAN-11	other	20
d	01-JAN-11	private	0

db<>fiddle here

In MySQL, you can use:

SELECT nd.name,
       nd.date,
       w.work_type,
       COALESCE(t.minutes, 0) AS minutes
FROM   (SELECT DISTINCT work_type FROM table1) w
       CROSS JOIN
       (SELECT DISTINCT name, date FROM table1) nd
       LEFT OUTER JOIN table1 t
       ON (t.name = nd.name AND t.date = nd.date AND t.work_type = w.work_type);

db<>fiddle here