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 |
