Display data from one table which is not available in another in SQL Server

lingers
 
on May 24, 2021 11:18 PM
689 Views

Displaying data from table 1 which is not available in Table

I want to display all the assest code from table 1 which are not in Table 2 together with all the baseno in table 2 whose active ='2'

Snap shot of Table 1

Snap shot of Table 2

snap shot of what i want

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 25, 2021 04:01 AM
on May 26, 2021 06:53 AM

Hi lingers,

Use UNION ALL operator. Check this sample query.

SQL

DECLARE @Table1 AS TABLE (Id INT,AssetCode VARCHAR(10),Colour VARCHAR(10))
INSERT INTO @Table1 VALUES(1,'S121','blue')
INSERT INTO @Table1 VALUES(2,'S122','yellow')
INSERT INTO @Table1 VALUES(3,'S123','green')
INSERT INTO @Table1 VALUES(4,'S124','white')
INSERT INTO @Table1 VALUES(5,'S125','red')
INSERT INTO @Table1 VALUES(6,'S126','orange')

DECLARE @Table2 AS TABLE (Id INT,BaseNo VARCHAR(10),Active INT)
INSERT INTO @Table2 VALUES(1,'S101',1)
INSERT INTO @Table2 VALUES(2,'S102',1)
INSERT INTO @Table2 VALUES(3,'S103',2)
INSERT INTO @Table2 VALUES(4,'S104',2)
INSERT INTO @Table2 VALUES(5,'S105',2)
INSERT INTO @Table2 VALUES(6,'S106',2)
INSERT INTO @Table2 VALUES(7,'S107',100)
INSERT INTO @Table2 VALUES(8,'S108',100)
INSERT INTO @Table2 VALUES(9,'S109',100)
INSERT INTO @Table2 VALUES(10,'S110',0)
INSERT INTO @Table2 VALUES(11,'S111',-1)
INSERT INTO @Table2 VALUES(12,'S112',-1)
INSERT INTO @Table2 VALUES(13,'S113',1)
INSERT INTO @Table2 VALUES(14,'S114',1)

SELECT AssetCode FROM @Table1 WHERE AssetCode NOT IN (SELECT BaseNo FROM @Table2)
UNION ALL
SELECT BaseNo FROM @Table2 WHERE Active = 2

Output

AssetCode
S121
S122
S123
S124
S125
S126
S103
S104
S105
S106