Hi sat,
You have to use IN clause insted of = in WHERE condition.
Refer below query.
DECLARE @Table1 AS TABLE (Col1 INT,Names VARCHAR(20))
INSERT INTO @Table1 VALUES(1001,'Peter')
INSERT INTO @Table1 VALUES(1002,'Ronnie')
INSERT INTO @Table1 VALUES(1005,'Peter')
INSERT INTO @Table1 VALUES(1009,'Peter')
INSERT INTO @Table1 VALUES(1005,'Ronnie')
DECLARE @Table2 AS TABLE (Col1 INT,FileName VARCHAR(50),Area VARCHAR(50),Country VARCHAR(50))
INSERT INTO @Table2 VALUES(1001,'AA1','North','South Africa')
INSERT INTO @Table2 VALUES(1002,'AA2','North','Sudan')
INSERT INTO @Table2 VALUES(1003,'AA3','South','Korea')
INSERT INTO @Table2 VALUES(1004,'AA4','East','Kenya')
INSERT INTO @Table2 VALUES(1005,'AA5','West','Ghana')
INSERT INTO @Table2 VALUES(1006,'AA6','West','Scotland')
INSERT INTO @Table2 VALUES(1007,'AA7','West','China')
INSERT INTO @Table2 VALUES(1008,'AA8','West','India')
INSERT INTO @Table2 VALUES(1009,'AA9','East','India')
INSERT INTO @Table2 VALUES(1010,'AA10','South','India')
SELECT * FROM @Table2 WHERE col1 IN (SELECT DISTINCT Col1 FROM @Table1 WHERE Names = 'Peter')
SELECT * FROM @Table2 WHERE col1 IN (SELECT DISTINCT Col1 FROM @Table1 WHERE Names = 'Ronnie')
Output
For Peter
Col1 FileName Area Country
1001 AA1 North South Africa
1005 AA5 West Ghana
1009 AA9 East India
For Ronnie
Col1 FileName Area Country
1002 AA2 North Sudan
1005 AA5 West Ghana