1 files changed, 29 insertions, 0 deletions
diff --git a/rushs/data-clash/step-3/napoleon_connections.sql b/rushs/data-clash/step-3/napoleon_connections.sql
new file mode 100644
index 0000000..170f635
--- /dev/null
+++ b/rushs/data-clash/step-3/napoleon_connections.sql
@@ -0,0 +1,29 @@
+SELECT
+    t.id AS individual_id,
+    t.name,
+    t.role,
+    t.affiliation AS current_affiliation,
+    p.affiliation AS historical_affiliation
+FROM surveillance_data.current_targets AS t
+INNER JOIN napoleon_data.historical_people AS p
+    ON
+        t.name = p.name
+        OR t.affiliation != 'N/A'
+WHERE
+    EXISTS (
+        SELECT 1 FROM (SELECT
+            a1.group_name_1,
+            a2.group_name_2
+        FROM surveillance_data.group_affinities AS a1,
+            surveillance_data.group_affinities AS a2
+        WHERE a1.group_name_2 = a2.group_name_1
+        UNION
+        SELECT
+            group_name_1,
+            group_name_2
+        FROM surveillance_data.group_affinities) AS test
+        WHERE
+            (group_name_1 = p.affiliation AND t.affiliation = group_name_2)
+            OR (group_name_1 = t.affiliation AND group_name_2 = p.affiliation)
+    )
+    OR t.affiliation = p.affiliation