If one column is empty then fetch data using another columns where condition in SQL Server

Question

I need to retrieve 3 columns from a table:

 OrderNumber, ParentOrderNumber, ClientName

ParentOrderNumber will always have ClientName, but some OrderNumber will not have a ClientName.

In that case I need to get the ClientName from the ParentOrderNumber.

Can you please help me with the SQL query?

Attached is the picture of the data scenario.

Here there is no client name available for ETA-454-5687 hence I need to fetch it using the parentOrderNumber (TOR-096-2000) from the table.

Answer 1

Use Right JOIN:

DECLARE @tbl1 as TABLE(
    OrderNumber  VARCHAR(50),
    ParentOrderNumber VARCHAR(50),
    ClientName VARCHAR(50)
)   

INSERT INTO @tbl1 VALUES('ETA-454-5687','TOR-096-2000','')
INSERT INTO @tbl1 VALUES('TOR-096-2442_XCODE',NULL,'')
INSERT INTO @tbl1 VALUES('TOR-096-2000',NULL,'ABCDEF')

SELECT
    T2.OrderNumber,
    T2.ParentOrderNumber,   
    CASE ISNULL(T1.ClientName,'') 
        WHEN '' THEN T2.ClientName
        ELSE T1.ClientName
    END AS ClientName
FROM @tbl1 T1
RIGHT JOIN @tbl1 T2 ON T1.OrderNumber=T2.ParentOrderNumber

Output: