Simplify a Select

Question

I have many databases where each one has the book tables and there is also a "Books (Libros)" table that contains details of each book such as the Subject (Tema), Author, and Editorial. These fields are linked via LEFT JOIN to their corresponding tables that are common to all databases.

I would like to simplify the SQL code because it can be very long and is always repetitive.

I would like to summarize it to:

SELECT TABLE1 FROM BASE1, TABLE2 FROM BASE2
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Temas ON [Libros].IdTema = Temas.IdTema 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Autores ON [Libros].IdAutor = Autores.IdAutor 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Editoriales ON [Libros].IdEditorial = Editoriales.IdEditorial 
WHERE [Libros].Estado IS NULL OR [Libros].Estado != 'X'

Real:

SELECT 'Esp Libros en Español' as Database_Name, * 
FROM [Esp Libros en Español].[dbo].[Libros]
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Temas ON [Libros].IdTema = Temas.IdTema 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Autores ON [Libros].IdAutor = Autores.IdAutor 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Editoriales ON [Libros].IdEditorial = Editoriales.IdEditorial 
WHERE [Esp Libros en Español].[dbo].[Libros].Estado IS NULL OR [Esp Libros en Español].[dbo].[Libros].Estado != 'X'

(continue)

UNION ALL SELECT 'Esp BOE - Agricultura, Ganadería, Pesca y Alimentación' as Database_Name, * 
FROM [Esp BOE - Agricultura, Ganadería, Pesca y Alimentación].[dbo].[Libros] 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Temas ON [Libros].IdTema = Temas.IdTema 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Autores ON [Libros].IdAutor = Autores.IdAutor 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Editoriales ON [Libros].IdEditorial = Editoriales.IdEditorial 
WHERE [Esp BOE - Agricultura, Ganadería, Pesca y Alimentación].[dbo].[Libros].Estado IS NULL OR [Esp BOE - Agricultura, Ganadería, Pesca y Alimentación].[dbo].[Libros].Estado != 'X'
ORDER BY Temas.Tema, Titulo

Answer 1

I think this script will be more clear and easy to maintain, but might not such simple like what you want, you can try it to see the result and performance.

WITH [Libros]
AS
(
--SERVER 1
	SELECT 'Esp Libros en Español' as Database_Name, *
	FROM [Esp Libros en Español].[dbo].[Libros] AS [L]
	WHERE [L].Estado IS NULL OR [L].Estado != 'X'
	UNION
--SERVER 2
	SELECT 'Esp BOE - Agricultura, Ganadería, Pesca y Alimentación' as Database_Name, *
	FROM [Esp BOE - Agricultura, Ganadería, Pesca y Alimentación].[dbo].[Libros] AS [L]
	WHERE [L].Estado IS NULL OR [L].Estado != 'X'
--	UNION
--  ALL OTHER SERVERS
)
SELECT * FROM 
[Libros]
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Temas ON [Libros].IdTema = Temas.IdTema 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Autores ON [Libros].IdAutor = Autores.IdAutor 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Editoriales ON [Libros].IdEditorial = Editoriales.IdEditorial 

Answer 2

I think this will be more clear and easy to maintain, but looks not like what you want, you can try it to see the result and performance:

WITH [Libros]
AS
(
--SERVER 1
	SELECT 'Esp Libros en Español' as Database_Name, *
	FROM [Esp Libros en Español].[dbo].[Libros] AS [L]
	WHERE [L].Estado IS NULL OR [L].Estado != 'X'
	UNION
--SERVER 2
	SELECT 'Esp BOE - Agricultura, Ganadería, Pesca y Alimentación' as Database_Name, *
	FROM [Esp BOE - Agricultura, Ganadería, Pesca y Alimentación].[dbo].[Libros] AS [L]
	WHERE [L].Estado IS NULL OR [L].Estado != 'X'
--	UNION
--  ALL OTHER SERVERS
)
SELECT * FROM 
[Libros]
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Temas ON [Libros].IdTema = Temas.IdTema 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Autores ON [Libros].IdAutor = Autores.IdAutor 
LEFT JOIN [Server\MSSQLSRV].[Libros].[dbo].Editoriales ON [Libros].IdEditorial = Editoriales.IdEditorial 

Answer 3

Hi @zequion

You may consider using temporary tables to deal with complex datasets in this scenario.

After splitting complex queries into temporary tables, this can simplify logic and make it easier to optimize queries.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".