SQL Server – How to Join Three Tables Sharing a Common Column

join;sql server

I have three tables with the following columns:

Table 1: Hostname, OS, Confidence    
Table 2: Hostname, Manufacturer, Model, Serial_Number, Architecture, Memory    
Table 3: Hostname, MAC, Interface

It should be, but I can't be certain yet, that if a Hostname exists on one table it will be on all three tables. I'm not sure why this data is separated into three different tables but for the moment it is, which is what leads to my question.

How can I combine these three tables into one? I have the following that'll combine two of them but I'm not sure how to modify it to include Table3:

SELECT
    COALESCE(Table1.Hostname, Table2.Hostname) AS Hostname,
    Manufacturer,
    Model,
    Serial_Number,
    Architecture,
    Memory
FROM Table1
FULL OUTER JOIN Table2
ON Table1.Hostname = Table2.Hostname

Best Answer

There are (at least) two ways to write FULL joins between more than 2 tables. Using FULL JOIN multiple times, the expression in the ON condition gets a bit longer but it's pretty simple:

SELECT
    COALESCE(t1.Hostname, t2.Hostname, t3.HostName) AS Hostname,
    t1.OS, 
    t1.Confidence, 
    t2.Manufacturer,  
    -- the rest, non common columns
FROM Table1 AS t1
  FULL OUTER JOIN Table2 AS t2
    ON t2.Hostname = t1.Hostname
  FULL OUTER JOIN Table3 AS t3
    ON t3.Hostname = COALESCE(t1.Hostname, t2.Hostname) ;

Using first a UNION of the common column, then LEFT joins:

SELECT
     u.Hostname,
    t1.OS, 
    t1.Confidence, 
    t2.Manufacturer,  
    -- the rest, non common columns
FROM 
    ( SELECT Hostname FROM Table1 UNION
      SELECT Hostname FROM Table2 UNION
      SELECT Hostname FROM Table3
    ) AS u 
  LEFT OUTER JOIN Table1 AS t1
    ON t1.Hostname = u.Hostname
  LEFT OUTER JOIN Table2 AS t2
    ON t2.Hostname = u.Hostname
  LEFT OUTER JOIN Table3 AS t3
    ON t3.Hostname = u.Hostname ;
Related Question