Hi smile,
Refer below query.
SQL
CREATE TABLE ##tblAnimal (AnimalID INT, TagID INT,AnimalName VARCHAR(10))
CREATE TABLE ##tblAbortion (AbrID INT,TagID INT,Milkable VARCHAR(10))
CREATE TABLE ##tblCategory (tblID INT, Category VARCHAR(20),TagID INT)
INSERT INTO ##tblAnimal VALUES(1,101,'A')
INSERT INTO ##tblAnimal VALUES(2,102,'B')
INSERT INTO ##tblAnimal VALUES(3,103,'C')
INSERT INTO ##tblAnimal VALUES(4,104,'D')
INSERT INTO ##tblAnimal VALUES(5,105,'E')
INSERT INTO ##tblAnimal VALUES(6,106,'F')
INSERT INTO ##tblAnimal VALUES(7,107,'G')
INSERT INTO ##tblAnimal VALUES(8,108,'H')
INSERT INTO ##tblAnimal VALUES(9,109,'I')
INSERT INTO ##tblAnimal VALUES(10,110,'J')
INSERT INTO ##tblAbortion VALUES(1,101,'Yes')
INSERT INTO ##tblAbortion VALUES(2,102,'Yes')
INSERT INTO ##tblAbortion VALUES(3,103,'No')
INSERT INTO ##tblCategory VALUES(1,'Add',104)
INSERT INTO ##tblCategory VALUES(2,'Add',105)
INSERT INTO ##tblCategory VALUES(3,'Remove',102)
INSERT INTO ##tblCategory VALUES(4,'Remove',108)
INSERT INTO ##tblCategory VALUES(5,'Remove',109)
INSERT INTO ##tblCategory VALUES(6,'Remove',110)
SELECT ROW_NUMBER() OVER(ORDER BY TagID) 'Sr.No',TagID
FROM ##tblAnimal
WHERE TagID NOT IN (
SELECT TagID FROM ##tblAbortion
WHERE Milkable = 'No'
UNION
SELECT TagID FROM ##tblCategory
WHERE Category = 'Remove'
)
Oputput
|
Sr.No
|
TagID
|
|
1
|
101
|
|
2
|
104
|
|
3
|
105
|
|
4
|
106
|
|
5
|
107
|
