Santosh-Sidd Santosh-Sidd - 2 months ago 7
SQL Question

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

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?

enter image description here

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

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:

enter image description here

Comments