Skip to content
Advertisement

Create a view in SQL Server and lookup with another table

I’m very new to SQL and am trying to create a view in SQL Server using 2 tables. There are some conditions will impact the way of selecting data and I would appreciate some guidance on what’s the logic to create a view for my requirements.

Condition: if the Description in Table1 is more than 10 characters and the character from the 9th to 11th are all numbers (3 numbers), then lookup in Table2 with the same Device_Name and Number. If there’s a match, select the Host_Name (T2) and replace the Device_Name in the view.

Example:

“Ethernet100” is more than 10 characters and there are all number from the 9th and 11th, use the number 100 and Device_name to lookup at Table2. Replace the Device_Name with Host_Name and put the Host_Name at the new column in the view [Host_On].

Table 1

Device_Name    Desrciption      Model 
---------------------------------------------
abc1           Ethernet100      sw1
abc2           Ethernet1/1/1    sw1
abc3           Ethernet         sw1

Table 2

Device_Name    Number   Model   Host_Name
-----------------------------------------
abc1           100      sw2     efg 

Desired result:

Device_Name    Description      Model   Host_On
------------------------------------------------
efg            Ethernet100      sw2     abc1
abc2           Ethernet1/1/1    sw1     [NULL]
abc3           Ethernet         sw1     [NULL]

Any advice or small part of code is appreciated.

Advertisement

Answer

SUBSTRING return empty string if string shorter than required.

TRY_CONVERT() return NULL if string isn’t numeric

SQL DEMO

SELECT   COALESCE (t2.Host_Name, t1.Device_Name) as Final_Device_Name
       , t1.[Description]
       , COALESCE (t2.Model, t1.Model) as Final_Model
       , t2.[Device_Name] as Host_On
       , SUBSTRING(t1.[Description], 9, 3) result       

FROM Table1 t1
LEFT JOIN Table2 t2
  ON TRY_CONVERT( INT, SUBSTRING(t1.[Description], 9, 3)) = [Number];

OUTPUT as you can see 1/1 doesnt match anything and get Host NULL

| Final_Device_Name |   Description | Final_Model | Host_On | result |
|-------------------|---------------|-------------|---------|--------|
|               efg |   Ethernet100 |         sw2 |    abc1 |    100 |
|              abc2 | Ethernet1/1/1 |         sw1 |  (null) |    1/1 |
|              abc3 |      Ethernet |         sw1 |  (null) |        |
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement