CODEWITHSUNDEEP
sqlsql-servert-sql
everydaylearn
everydaylearn

Reputation: 143

Select from another table if record not found in the first table - SQL Server 2012

Here is the situation. I have two parameters to query: Item_code Item_type

The first_table contains: Item_Code,Item_Characteristics

The second_table contains: Item_Type,Item_Characteristics

My goal is to get the item_characteristics. If the specific item is not found in the first table, I would want to use the Item_type to get them from the second table.

Any way this can be done in a single query?

I am using SQL Server 2012.

Upvotes: 0

Views: 2250

Answers (3)

sam
sam

Reputation: 1304

Let me know if this works - 

select isnull(a.Item_Characteristics,b.item_type)
from first_table as a
left join second_table as b
on a.Item_Characteristics = b.Item_Characteristics
where a.Item_code = @itemcode and b.Item_type = @itemtype

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

One way of doing this uses not exists:

select t1.Item_Characteristics
from t1
where t1.item_code = @Item_Code
union all
select t2.Item_Characteristics
from t2
where t2.item_type = @Item_Type and
      not exists (select 1 from t1 where t1.item_code = @Item_Code);

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can try with a FULL JOIN in case Item_Code and Item_Type are of the same type:

SELECT COALESCE(t1.Item_Characteristics, t2.Item_Characteristics) AS Item_Characteristics
FROM table1 AS t1
FULL JOIN table2 AS t2 
  ON t1.Item_Code = t2.Item_Type
WHERE COALESCE(t1.Item_Code, t2. Item_Type) = @param

Upvotes: 2

Related Questions