patternsqlMinor
Join three tables that share a common column
Viewed 0 times
threetablescolumnjointhatsharecommon
Problem
I have three tables with the following columns:
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:
Table 1: Hostname, OS, Confidence
Table 2: Hostname, Manufacturer, Model, Serial_Number, Architecture, Memory
Table 3: Hostname, MAC, InterfaceIt 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.HostnameSolution
There are (at least) two ways to write
Using first a
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 ;Code Snippets
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) ;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 ;Context
StackExchange Database Administrators Q#152806, answer score: 8
Revisions (0)
No revisions yet.