Select records from multiple tables based on condition in SQL Server

Last Reply 6 months ago By dharmendr

Posted 6 months ago

issue in fetching record from multiple tables with condition using sql query

Conditions:

Data will be fetched from these three tables with following conditions

From tblAbortion Only those TagID will be fetched where Milkable = Yes Example 101 and 102

From tblCategory Only those TagID will be fetched where Category = Add Example 104 and 105

In tblCategory TagID 102 = Remove so it will be exempted / drop. Hence final TagID 101,104,105 will be obtained from tblAbortion and tblCategory. Remaining TagID will be fetched from tblAnimal such as 106 and 107.

tblAnimal

AnimalID TagID AnimalName
1 101 A
2 102 B
3 103 C
4 104 D
5 105 E
6 106 F
7 107 G
8 108 H
9 109 I
10 110 J

tblAbortion

AbrID TagID Milkable
1 101 Yes
2 102 Yes
3 103 No

tblCategory

tblID Category TagID
1 Add 104
2 Add 105
3 Remove 102
4 Remove 108
5 Remove 109
6 Remove 110

Required Output

Sr.No TagID
1 101
2 104
3 105
4 106
5 107

How it can be get????

Posted 6 months ago

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