CODEWITHSUNDEEP
sql-server
Santosh-Sidd
Santosh-Sidd

Reputation: 49

If one column is empty then fetch data using another column in sql server

In a table i need to retrive 3 columns :

 OrderNumber, ParentOrderNumber, ClientName

ParentOrderNumber will always have ClientName
But some OrderNumber will not have ClientName,

In that case i need to get ClientName from ParentOrderNumber.

Can you please help me with 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 same table.

Upvotes: 1

Views: 2132

Answers (5)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use Right Join:

Please refer this one look like same question:https://stackoverflow.com/a/39543010/6606630

Upvotes: 0

Sludovic
Sludovic

Reputation: 1

I think we need more information from your table. Have you something like this ?

OrderNumber|ParentOrderNumber|ClientName
1|NULL|nameOfTheClient
2|1|NULL

Then you will need to join your table

select  a.OrderNumber, a.ParentOrderNumber, ISNULL(a.ClientName,b.ClientName) 
from yourTable a
left join yourTable b on b.OrderNumber = a.ParentOrderNumber

Upvotes: 0

Dheebs
Dheebs

Reputation: 408

This can be achieved with a Case Statement

It performs a basic True False style logic and then allows an action to be performed. 

Select
    ClientName
,   ParentOrderNumber
,   CASE WHEN [OrderNumber] IS NULL THEN ParentOrderNumber ELSE OrderNumber END AS [OrderNumber]
FROM
    tbl_Table

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7753

Use COALESCE which is shorthand for a CASE expression:

COALESCE(expression1, expression2, ..., expressionN) is shorthand for

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

therefore you can use

SELECT ParentOrderNumber,
       clientname,
       COALESCE(OrderNumber, ParentOrderNumber) as OrderNumber
FROM MyTable

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

select 
ParentOrderNumber,
clientname,
case when OrderNumber is null or len(OrderNumber)=0 then clientname else OrderNumber end as OrderNumber
from yourtable

Upvotes: 0

Related Questions