I will have to assume that addressID= 11 is the same across all tables - else the question does not really make sense. This ends up a four-way full join - not for the faint of heart.
; WITH FJ1 AS ( SELECT AdressID = isnull(T1.AdressID, T2.AdressID), T1.Phone1, T1. Phone2, T2.Email FROM T1 FULL JOIN T2 ON T1.AdressID = T2.AdressID ), FJ2 AS ( SELECT AdressID = isnull(T3.AdressID, T4.AdressID), T3.Fax, T4.Mobile FROM T3 FULL JOIN T4 ON T3.AdressID = T4.AdressID ) SELECT AdressID = isnull(FJ1.AdressID, FJ2.AdressID), FJ1.Phone, FJ1.Phone2, FJ1.Email, FJ2Fax, FJ2.Mobile FROM FJ1 FULL JOIN FJ2 ON FJ1.AdressID = FJ2.AdressID
Then that's why we use FULL JOIN, it will return rows even though id not matched or null.
Or maybe i misunderstood your request.
Thanks for the elaboration.
I'm uncertain about your comment. You say that the joins are not working, but you have still accepted my Answer.
If you need more help, please provide CREATE TABLE statements for your tables and the sample data as INSERT statements. Since I cannot copy and paste from an image, it was not possible for me to test my query.
Sign in to comment