'How to select distinct from two tables and then join them

I've seen similar questions, but none of the fits my issue.

I have two tables:

WorkTable

WorkCode WorkDescription
220001 WorkDescription1
220002 WorkDescription2
220003 WorkDescription3
220004 WorkDescription4

TasksTable

TaskCode WorkCode TaskDescription
AV0001 TaskDescription1
AV0002 220002 TaskDescription2
AV0003 220003 TaskDescription3
AV0004 220003 TaskDescription4

I need to get this results

ResultTable

TaskCode WorkCode TaskDescription WorkDescription
220001 WorkDescription1
AV0001 TaskDescription1
220002 AV0002 TaskDescription2 WorkDescription2
220003 AV0003 TaskDescription3 WorkDescription3
220003 AV0004 TaskDescription4 WorkDescription3
220004 WorkDescription4

I've tried several queries, but it lefts out the TaskCodes that have no WorkCode associated:

WrongResultTable

TaskCode WorkCode TaskDescription WorkDescription
220001 WorkDescription1
220002 AV0002 TaskDescription2 WorkDescription2
220003 AV0003 TaskDescription3 WorkDescription3
220003 AV0004 TaskDescription4 WorkDescription3
220004 WorkDescription4

Can someone help me? Thank you

sqljoindistinct


Solution 1:[1]

It's not very elegant, but see below.

--This section is just creating the temp tables with the data you provided.
DECLARE @WorkTable TABLE (
WorkCode INT, 
WorkDescription NVARCHAR(50))

INSERT INTO @WorkTable (WorkCode, WorkDescription)
VALUES (220001, 'WorkDescription1') , (220002, 'WorkDescription2'), (220003, 'WorkDescription3'),  (220004, 'WorkDescription4')


DECLARE @TasksTable TABLE (
TaskCode NVARCHAR(50), 
WorkCode INT ,
TaskDescription NVARCHAR(50))

INSERT INTO @TasksTable (TaskCode, WorkCode, TaskDescription)
VALUES ('AV0001', NULL, 'TaskDescription1') , ('AV0002', 220002, 'TaskDescription2'), ('AV0003', 220003, 'TaskDescription3'),  ('AV0004', 220003, 'TaskDescription4')

;

--Actual SQL query starts here
WITH a as (
select distinct w.WorkCode, t.TaskCode, t.TaskDescription, w.WorkDescription
from @WorkTable w
LEFT JOIN @TasksTable t
    on t.WorkCode = w.WorkCode) ,

b as (
SELECT distinct a.WorkCode, tt.TaskCode, tt.TaskDescription, a.WorkDescription
FROM @TasksTable tt
left join a 
        on tt.WorkCode = a.WorkCode) ,

c as (
     
select *
from a

union all 

select *
from b)

select distinct c.WorkCode, c.TaskCode, c.TaskDescription, c.WorkDescription
from c

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 trice

Related Questions