Filter records from multiple Tables in SQL Server

Hey PRA,

Please refer below query.

SQL

DECLARE @Fruits AS TABLE(Id INT,Name VARCHAR(10),Price INT)
INSERT INTO @Fruits VALUES(1,'Apple',5)
INSERT INTO @Fruits VALUES(2,'Orange',13)
INSERT INTO @Fruits VALUES(3,'Oil',25)
INSERT INTO @Fruits VALUES(4,'Cherry',12)
  
DECLARE @Person AS TABLE(Id INT,Person VARCHAR(10),IdFood VARCHAR(50),NumberKg VARCHAR(50))
INSERT INTO @Person VALUES(1,'Rustam','1,2,4','2,3,1')
INSERT INTO @Person VALUES(2,'Firuzjon','2,3,1,4','1,1,1,1')
INSERT INTO @Person VALUES(3,'Sadriddin','4','3')
 
DECLARE @Cars AS TABLE(Id INT,Car VARCHAR(10),Price INT)
INSERT INTO @Cars VALUES(5,'musso',500)
INSERT INTO @Cars VALUES(6,'mersedes',700)
INSERT INTO @Cars VALUES(7,'tayota',300)
 
DECLARE @var VARCHAR(10) 
SET @var = 'Cherry'
select f.Name from @Fruits f 
left outer join @Person p on p.Id=f.Id 
left outer join @Cars c on p.Id=c.Id 
where f.Name = @var or p.Person = @var or c.Car = @var

DECLARE @Fruits AS TABLE(Id INT,Name VARCHAR(10),Price INT)
INSERT INTO @Fruits VALUES(1,'Apple',5)
INSERT INTO @Fruits VALUES(2,'Orange',13)
INSERT INTO @Fruits VALUES(3,'Oil',25)
INSERT INTO @Fruits VALUES(4,'Cherry',12)
  
DECLARE @Person AS TABLE(Id INT,Person VARCHAR(10),IdFood VARCHAR(50),NumberKg VARCHAR(50))
INSERT INTO @Person VALUES(1,'Rustam','1,2,4','2,3,1')
INSERT INTO @Person VALUES(2,'Firuzjon','2,3,1,4','1,1,1,1')
INSERT INTO @Person VALUES(3,'Sadriddin','4','3')
 
DECLARE @Cars AS TABLE(Id INT,Car VARCHAR(10),Price INT)
INSERT INTO @Cars VALUES(5,'musso',500)
INSERT INTO @Cars VALUES(6,'mersedes',700)
INSERT INTO @Cars VALUES(7,'tayota',300)
 
declare @var varchar(10) = 'musso'
select * from(
	select name from @Fruits
	union
	select person as Name from @Person
	union
	select car as Name from @Cars
)as t
    where Name = @var