Get all data from multiple table in SQL Server

Hi Nurali,

Please refer below query which full fills your requirement

SQL

DECLARE @Persons AS TABLE(ID INT,NAME VARCHAR(30))
INSERT INTO @Persons VALUES(1,'Rustam')
INSERT INTO @Persons VALUES(2,'Firuz')
INSERT INTO @Persons VALUES(3,'Suhrob')

DECLARE @Fruits AS TABLE(ID INT,NAME VARCHAR(30))
INSERT INTO @Fruits VALUES(1,'Apple')
INSERT INTO @Fruits VALUES(2,'Orange')
INSERT INTO @Fruits VALUES(3,'Cherry')
INSERT INTO @Fruits VALUES(4,'Melon')

DECLARE @Mixed AS TABLE(ID INT,PersonID INT,FruitsID INT,Color VARCHAR(20))
INSERT INTO @Mixed VALUES(1,1,1,'Red')
INSERT INTO @Mixed VALUES(2,1,2,'Yellow')
INSERT INTO @Mixed VALUES(3,1,3,'Yellow')
INSERT INTO @Mixed VALUES(4,3,3,'Red')
INSERT INTO @Mixed VALUES(5,3,4,NULL)

DECLARE @Register AS TABLE(ID INT,PersonID INT,FruitsID INT,Color VARCHAR(20))
INSERT INTO @Register VALUES(1,1,1,'Red')
INSERT INTO @Register VALUES(2,1,2,NULL)
INSERT INTO @Register VALUES(3,3,3,'Red')
INSERT INTO @Register VALUES(4,3,4,'Green')

;WITH cte AS
(SELECT DISTINCT m.PersonID,m.FruitsID,m.Color FROM @Mixed m
INNER JOIN @Register r ON m.PersonID = r.PersonID
WHERE m.PersonID = 1 
UNION ALL
SELECT DISTINCT r.PersonID,r.FruitsID,r.Color FROM @Mixed m
INNER JOIN @Register r ON m.PersonID = r.PersonID
WHERE m.PersonID = 1 AND r.Color IS NULL
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS Id,p.NAME AS PersonId,f.NAME AS FruitsId,ISNULL(cte.Color,'') AS Color FROM cte
INNER JOIN @Persons p ON cte.PersonID = p.ID
INNER JOIN @Fruits f ON cte.FruitsID = f.ID

Output