Q:

T-SQL query that includes missing records

Apologies if this a little confusing to follow but hopefully someone can help me with this.  For some reason my mind is drawing a blank on this and also typing the problem out might help.
I am doing some ETL stuff and I have data that can be grouped by a specific Id value.  Each record in my dataset has a LookupId value that I use to join to another table and get additional information.  Pretty standard INNER JOIN stuff here.  However, I need to also include missing LookupId values in my dataset.  This turns my INNER JOIN into a LEFT JOIN which is also pretty standard stuff.
You can use the following sample schema and data below to get an idea of what I am looking to do:
-- Create sample schema
CREATE TABLE ETL_DataSet (
    Id INT NOT NULL,
    LookupId INT NULL,
    LookupCode CHAR(1) NULL,
    LookupDesc NVARCHAR(40) NULL
);

CREATE TABLE ETL_LookupTable (
    LookupId INT NOT NULL
    , LookupCode CHAR(1) NOT NULL
    , LookupDesc NVARCHAR(40) NOT NULL
    , CONSTRAINT PK_LookupTable_LookupId PRIMARY KEY CLUSTERED (LookupId)
);

-- Insert sampel data
INSERT INTO ETL_LookupTable (LookupId, LookupCode, LookupDesc) VALUES
    (1, 'Z', 'Z Description'),
    (2, 'A', 'A Description'),
    (3, 'B', 'B Description'),
    (4, 'C', 'C Description'),
    (5, 'D', 'D Description'),
    (6, 'E', 'E Description'),
    (7, 'X', 'X Description');

INSERT INTO ETL_DataSet (Id, LookupId, LookupCode, LookupDesc) VALUES 
    (1, 3, 'B', 'B Description'),
    (1, 5, 'D', 'D Description'),
    (1, 3, 'B', 'B Description'),
    (1, 2, 'A', 'A Description'),
    (2, 4, 'C', 'C Description'),
    (2, 6, 'E', 'E Description'),
    (2, 3, 'B', 'B Description'),
    (2, 2, 'A', 'A Description');

And here is the sample script to run:
DECLARE @id INT = 1; //change this to 1 or 2
;WITH LookupCTE AS
(
    SELECT d.*
    FROM ETL_LookupTable l INNER JOIN ETL_DataSet d ON l.LookupId = d.LookupId
    WHERE d.Id = @id --comment this out to get all data in ETL_DataSet
)
SELECT *
FROM ETL_LookupTable l LEFT JOIN LookupCTE cte ON l.LookupId = cte.LookupId

The end goal is to include all LookupTable values for each Id even if the set of Ids do not have the LookupTable values.  Changing @Id between 1 and 2 you can see that this works but if you comment out the WHERE clause in the CTE, you get incorrect data.  I am trying to do this without using cursors or any other row-by-agonizing-row technique so hopefully someone can assist or point me in the right direction.
Desired results should look like the following:
Id          LookupId    LookupCode LookupDesc
----------- ----------- ---------- ----------------------------------------
1           2           A          A Description
1           3           B          B Description
1           3           B          B Description
1           NULL        C          C Description
1           5           D          D Description
1           NULL        E          E Description
1           NULL        X          X Description
1           NULL        Z          Z Description
2           2           A          A Description
2           3           B          B Description
2           4           C          C Description
2           NULL        D          D Description
2           6           E          E Description
2           NULL        X          X Description
2           NULL        Z          Z Description

A:

You seem to want a Cartesian product of the etl_dataset.id with the etl_lookupTable information, so a cross join forming that product should work.

select
ed.*, el.*, d.*
from (select distinct id from etl_dataset) ed
cross join (select distinct LookupId, LookupCode, LookupDesc from ETL_LookupTable) el
left join etl_dataset d on ed.id = d.id and el.LookupId = d.LookupId
order by 
ed.id, el.LookupCode
GO

id | LookupId | LookupCode | LookupDesc    |   Id | LookupId | LookupCode | LookupDesc   
-: | -------: | :--------- | :------------ | ---: | -------: | :--------- | :------------
 1 |        2 | A          | A Description |    1 |        2 | A          | A Description
 1 |        3 | B          | B Description |    1 |        3 | B          | B Description
 1 |        3 | B          | B Description |    1 |        3 | B          | B Description
 1 |        4 | C          | C Description | null |     null | null       | null         
 1 |        5 | D          | D Description |    1 |        5 | D          | D Description
 1 |        6 | E          | E Description | null |     null | null       | null         
 1 |        7 | X          | X Description | null |     null | null       | null         
 1 |        1 | Z          | Z Description | null |     null | null       | null         
 2 |        2 | A          | A Description |    2 |        2 | A          | A Description
 2 |        3 | B          | B Description |    2 |        3 | B          | B Description
 2 |        4 | C          | C Description |    2 |        4 | C          | C Description
 2 |        5 | D          | D Description | null |     null | null       | null         
 2 |        6 | E          | E Description |    2 |        6 | E          | E Description
 2 |        7 | X          | X Description | null |     null | null       | null         
 2 |        1 | Z          | Z Description | null |     null | null       | null         

dbfiddle here

