SQL JOIN and UNION Combined

Question

I want to connect my for tables (picture below) When I use the first and second code all works but i do not have all data that i need.

FIRST CODE WORKS

SELECT *
FROM Erste.dbo.Persons1$ 
LEFT OUTER JOIN Erste.dbo.Adresses1$ 
ON Erste.dbo.Persons1$.Id=Erste.dbo.Adresses1$.Id 
--LEFT OUTER JOIN Erste.dbo.Adresses2$
--ON Erste.dbo.Persons1$.Id=Erste.dbo.Adresses2$.Id
UNION
SELECT * 
From Erste.dbo.Persons2$
LEFT OUTER JOIN Erste.dbo.Adresses2$
ON Erste.dbo.Persons2$.Id=Erste.dbo.Adresses2$.Id

SECOND CODE WORKS

SELECT *
FROM Erste.dbo.Persons1$ 
LEFT OUTER JOIN Erste.dbo.Adresses1$ 
ON Erste.dbo.Persons1$.Id=Erste.dbo.Adresses1$.Id 
--LEFT OUTER JOIN Erste.dbo.Adresses2$
--ON Erste.dbo.Persons1$.Id=Erste.dbo.Adresses2$.Id
UNION
SELECT * 
From Erste.dbo.Persons2$
LEFT OUTER JOIN Erste.dbo.Adresses2$
ON Erste.dbo.Persons2$.Id=Erste.dbo.Adresses2$.Id

First SELECT combined don't work

SELECT *
FROM Erste.dbo.Persons1$ 
LEFT OUTER JOIN Erste.dbo.Adresses1$ 
ON Erste.dbo.Persons1$.Id=Erste.dbo.Adresses1$.Id 
LEFT OUTER JOIN Erste.dbo.Adresses2$
ON Erste.dbo.Persons1$.Id=Erste.dbo.Adresses2$.Id
UNION
SELECT * 
From Erste.dbo.Persons2$
LEFT OUTER JOIN Erste.dbo.Adresses2$
ON Erste.dbo.Persons2$.Id=Erste.dbo.Adresses2$.Id

Error: Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Completion time: 2024-02-27T18:25:24.6084257+01:00

Tables and my code from SQL Server Managment Studio

I'm trying to combine these 4 tables. Can You help me?

Answer 1

Thank you so much xQbert for the help. I did it in this way.

SELECT FirstName, SecondName, Ulica, Numer
FROM Erste.dbo.Persons1$, Erste.dbo.Adresses1$
WHERE Erste.dbo.Persons1$.ID=Erste.dbo.Adresses1$.ID
UNION
SELECT FirstName, SecondName, Ulica, Numer
FROM Erste.dbo.Persons1$, Erste.dbo.Adresses2$ 
WHERE Erste.dbo.Persons1$.ID=Erste.dbo.Adresses2$.ID
UNION
SELECT FirstName, SecondName, Ulica, Numer
FROM Erste.dbo.Persons2$, Erste.dbo.Adresses2$ 
WHERE Erste.dbo.Persons2$.ID=Erste.dbo.Adresses2$.ID
ORDER BY Numer ASC