5

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
4
  • Are there any PK, UQ, FK constraints in the 3 tables? And what are their names? I surely hope not table1, 2, 3. Commented Oct 19, 2016 at 22:17
  • No, they aren't named table1, table2, though the actual names aren't that helpful to anyone that doesn't use Nessus regularly. They are named Stage_11936, Stage_24270, and Stage_24272. These tables correspond to Plugin IDs from our Nessus vulnerability scanner. For each of these tables the Hostname is the PK, no FKs on any of them.
    – Tchotchke
    Commented Oct 19, 2016 at 22:28
  • I aksed about the FKs because I hoped there might be FKs between them or to a 4th table. There might still be a 4th table that holds all the Hostnames (from all 3) and the FKs are just not defined. Check if the Hostname appears in other tables as well. Commented Oct 19, 2016 at 22:36
  • Hostname does appear in one other table, Vulnerabilities, but it's not a PK, UQ, or FK there. I'm inheriting this database from the previous guy and I'm a neophyte at databases and their design - This could probably be better designed and some follow up questions are probably going to address some of that (and teach me a bunch in the process).
    – Tchotchke
    Commented Oct 19, 2016 at 23:09

1 Answer 1

8

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 ;
1
  • I used the first example, it looks cleaner to me and I more readily follow it. It also worked like a charm! Thanks!
    – Tchotchke
    Commented Oct 21, 2016 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.