Two options come to mind... #1 Simply add a "ProdType" column to the LineItems table or #2 use a sequence object to populate the ids for all product tables.
#1 should be easy enough to grasp. Use "ProdType to conditionally join to each of the different product tables. Similar to the following...
SELECT
pli.PurchaseId,
pli.ProductID,
ProductName = COALESCE(ta.RandomProductName, tb.RandomProductName, tc.RandomProductName),
pli.Quantity
FROM
dbo.PurchaseLineItem pli
LEFT JOIN dbo.Table_A ta
ON pli.ProductID = ta.ProductID
AND pli.ProdType = 'A'
LEFT JOIN dbo.Table_B tb
ON pli.ProductID = tb.ProductID
AND pli.ProdType = 'B'
LEFT JOIN dbo.Table_C tc
ON pli.ProductID = tc.ProductID
AND pli.ProdType - 'C';
#2 is just as simple but not many people are familiar how to use a SEQUENCE object to create distributed keys across multiple tables So I'll provide a more robust example...
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.ProductSEQ', 'SO') IS NOT NULL DROP SEQUENCE dbo.ProductSEQ;
CREATE SEQUENCE dbo.ProductSEQ
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1;
IF OBJECT_ID('tempdb.dbo.Table_A', 'U') IS NOT NULL DROP TABLE dbo.Table_A;
CREATE TABLE dbo.Table_A (ProductID INT NOT NULL PRIMARY KEY, RandomProductName VARCHAR(20) NOT NULL);
IF OBJECT_ID('tempdb.dbo.Table_B', 'U') IS NOT NULL DROP TABLE dbo.Table_B;
CREATE TABLE dbo.Table_B (ProductID INT NOT NULL PRIMARY KEY, RandomProductName VARCHAR(20) NOT NULL);
IF OBJECT_ID('tempdb.dbo.Table_C', 'U') IS NOT NULL DROP TABLE dbo.#Table_C;
CREATE TABLE dbo.Table_C (ProductID INT NOT NULL PRIMARY KEY, RandomProductName VARCHAR(20) NOT NULL);
IF OBJECT_ID('tempdb.dbo.PurchaseLineItem', 'U') IS NOT NULL DROP TABLE dbo.PurchaseLineItem;
CREATE TABLE dbo.PurchaseLineItem (
PurchaseId INT NOT NULL,
-- add FK constraint to PurchaseHeader table
ProductID INT NOT NULL,
Quantity INT NOT NULL,
CONSTRAINT pk_PurchaseLineItem PRIMARY KEY (PurchaseId, ProductID)
);
--============================================================================================
INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-1');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-1');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-1');
INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-2');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-2');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-2');
INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-3');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-3');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-3');
INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-4');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-4');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-4');
INSERT dbo.Table_A (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'A-5');
INSERT dbo.Table_B (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'B-5');
INSERT dbo.Table_C (ProductID, RandomProductName) VALUES (NEXT VALUE FOR ProductSEQ, 'C-5');
INSERT dbo.PurchaseLineItem (PurchaseId, ProductID, Quantity) VALUES
(123455, 1, 3), (123456, 2, 1), (112233, 3, 1), (32123, 4, 1),
(123455, 5, 3), (123456, 6, 1), (112233, 7, 1), (32123, 8, 1),
(123455, 8, 3), (123456, 10, 1), (112233, 11, 1), (32123, 12, 1);
SELECT * FROM dbo.Table_A ta;
SELECT * FROM dbo.Table_B tb;
SELECT * FROM dbo.Table_C tc;
--============================================================================================
SELECT
pli.PurchaseId,
pli.ProductID,
ProductName = COALESCE(ta.RandomProductName, tb.RandomProductName, tc.RandomProductName),
pli.Quantity
FROM
dbo.PurchaseLineItem pli
LEFT JOIN dbo.Table_A ta
ON pli.ProductID = ta.ProductID
LEFT JOIN dbo.Table_B tb
ON pli.ProductID = tb.ProductID
LEFT JOIN dbo.Table_C tc
ON pli.ProductID = tc.ProductID;