SQL column merge with conditions and without duplicates-CodePudding

Trying to combine 2 tables into 1 results table without having duplicate entries.
Conditions are:
1. For each t1name look for each days in t2date, and if there is 1 in t2update, use that info. If there is no 1, then use the 0 row.
2. If the t1name doesn't exist in t2name, create it with the t1date, and set t2update as 0.

Here the are tables examples:

| t1name | t1date     | t1department |
| ------ | ---------- | ------------ |
| name 1 | 2000.01.01 | tlc          | 
| name 1 | 2000.01.01 | tlc          |
| name 2 | 2000.01.04 | non-tlc      |
| name 3 | 2000.01.04 | non-tlc      |
| name 4 | 2000.01.04 | tlc          |
| name 5 | 2000.01.04 | tlc          |
| name 6 | 2000.01.04 | tlc          |
| name 7 | 2000.01.04 | tlc          |

Table 1

| t2name | t2update | t2date       |
| ------ | -------- | ------------ |
| name 1 | 1        | 2000.01.01   |
| name 1 | 0        | 2000.01.02   | 
| name 1 | 1        | 2000.01.02   | 
| name 2 | 1        | 2000.01.04   | 
| name 2 | 0        | 2000.01.04   | 
| name 2 | 0        | 2000.01.09   | 
| name 3 | 0        | 2000.01.09   | 
| name 3 | 1        | 2000.01.05   | 
| name 4 | 0        | 2000.01.03   |

Table 2

| rname  | rupdate | rdate        |
| ------ | ------- | ------------ |
| name 1 | 1       | 2000.01.01   |
| name 1 | 1       | 2000.01.02   | 
| name 2 | 1       | 2000.01.04   | 
| name 3 | 0       | 2000.01.02   | 
| name 3 | 1       | 2000.01.05   | 
| name 4 | 0       | 2000.01.03   | 
| name 5 | 0       | 2000.01.09   | 
| name 6 | 0       | 2000.01.09   | 
| name 7 | 0       | 2000.01.09   |

Results table

Currently using following:

CREATE OR REPLACE VIEW "rtable" AS 
(
   SELECT DISTINCT
      ((CASE WHEN (t2.t2updates) > 0) AND (MAX(t1.t1date))) THEN name
    , t1.date
    , t1.t1department
    , t2.updates 
FROM (table1 t1
LEFT JOIN table2 t2 on (t2.t2name = t1.t1name))
GROUP BY 
    , t1.date
    , t1.t1department
    , t2.updates
ORDER BY t1.t1name ASC
)

And the results return duplicate values per day. For single t1name and t1date have both entries for 1 and 0. Need the outcome in a specific way so it I can do KPI on the updates of the VMs.

CodePudding user response：

After simplifying the approach, and plenty of trials and error, simple right join did the job

CREATE OR REPLACE VIEW "rtable" AS 
(
   SELECT
      t2.name
    , t2.date
    , t1.t1department
    , t2.updates 
FROM 
    (table1 t1
Right JOIN table2 t2 on (t2.t2name = t1.t1name) AND (t2.date = t1.date)))
)