Multi-target Query Assistance

I ran into a scenario with a database and I'm hoping the smart people here could help me out with a "best" way to handle the lookup.

The question: What would be the best way to query across different "target" tables based on a field from the source table.

I've got a few tables that look something like this:

ContentId ContentTypeId IsEnabled
Content Table (the source)
E454820D-A695-47CC-9AFA-02FE581DCDD1 9262536B-49A3-4494-802F-04DFF10424ED 1
9E2D29B7-D8EA-4369-830C-0906A63A1B99 9262536B-49A3-4494-802F-04DFF10424ED 1
3FBE607E-31A8-4D4D-A309-0BED544F6199 F7D226AB-D59F-475C-9D22-4A79E3F0EC07 1
EBF10469-83F0-4666-9477-1234BE10A841 F586769B-0822-468A-B7F3-A94D480ED9B0 1
3274AE49-0B3B-45AF-99EF-1AF71412A4EF 46448885-D0E6-4133-BBFB-F0CD7B0FD6F7 1
5308ABB1-03E3-484A-943F-1BBEC44119C2 F586769B-0822-468A-B7F3-A94D480ED9B0 0
622B3BEC-8A74-4789-BC6E-213C4D14047F F7D226AB-D59F-475C-9D22-4A79E3F0EC07 1
D1908CF8-1EF2-4F8E-B314-2A037557BEDE 9262536B-49A3-4494-802F-04DFF10424ED 0
6AF53B6C-1273-4154-9538-33B7E6C92BA8 A0753CFB-923B-4975-AD2A-42E5282A6D5D 0
0F814B78-7246-466B-9255-34F6BB2ADD48 46448885-D0E6-4133-BBFB-F0CD7B0FD6F7 1

ContentID ContentTypeID UserId CreatedDateUtc
Content1 (one of the targets)
622B3BEC-8A74-4789-BC6E-213C4D14047F F7D226AB-D59F-475C-9D22-4A79E3F0EC07 1234 2023-01-01 16:38:32.111
3FBE607E-31A8-4D4D-A309-0BED544F6199 F7D226AB-D59F-475C-9D22-4A79E3F0EC07 2345 2022-04-27 09:14:01.978

ContentID ContentTypeID UserId CreatedDateUtc
Content2 (one of the targets)
0F814B78-7246-466B-9255-34F6BB2ADD48 46448885-D0E6-4133-BBFB-F0CD7B0FD6F7 91478 2012-09-27 22:45:51.521
3274AE49-0B3B-45AF-99EF-1AF71412A4EF 46448885-D0E6-4133-BBFB-F0CD7B0FD6F7 4567 2014-04-27 11:22:33.444

The first "Content" table has millions upon millions of records.  The Content1 and Content2 tables have a subset, but contain the details I need.

I'm trying to find the most efficient way to connect the Content table to both the Content1 AND Content2 tables so my output would look something like:

ContentId ContentTypeId IsEnabled UserID CreatedDateUtc
Desired Result Set
E454820D-A695-47CC-9AFA-02FE581DCDD1 9262536B-49A3-4494-802F-04DFF10424ED 1 1234 2012-01-06 12:00:00.00
9E2D29B7-D8EA-4369-830C-0906A63A1B99 9262536B-49A3-4494-802F-04DFF10424ED 1 5678 etc...
3FBE607E-31A8-4D4D-A309-0BED544F6199 F7D226AB-D59F-475C-9D22-4A79E3F0EC07 1 etc...
EBF10469-83F0-4666-9477-1234BE10A841 F586769B-0822-468A-B7F3-A94D480ED9B0 1
3274AE49-0B3B-45AF-99EF-1AF71412A4EF 46448885-D0E6-4133-BBFB-F0CD7B0FD6F7 1
5308ABB1-03E3-484A-943F-1BBEC44119C2 F586769B-0822-468A-B7F3-A94D480ED9B0 0
622B3BEC-8A74-4789-BC6E-213C4D14047F F7D226AB-D59F-475C-9D22-4A79E3F0EC07 1
D1908CF8-1EF2-4F8E-B314-2A037557BEDE 9262536B-49A3-4494-802F-04DFF10424ED 0
6AF53B6C-1273-4154-9538-33B7E6C92BA8 A0753CFB-923B-4975-AD2A-42E5282A6D5D 0
0F814B78-7246-466B-9255-34F6BB2ADD48 46448885-D0E6-4133-BBFB-F0CD7B0FD6F7 1

There are 28 possible connections from the Content table to other tables.

I've tried a host of things, but the performance is abysmal.  I'm asking this clever bunch: What would be the ideal way to do this work?