From c9b6b9a5ca082fe7c1b6f58d7713f785a9eb6a5c Mon Sep 17 00:00:00 2001
From: Martial Simon <msimon_fr@hotmail.com>
Date: Mon, 15 Sep 2025 01:08:27 +0200
Subject: add: graphs et rushs

---
 rushs/data-clash/step-3/napoleon_connections.sql | 29 ++++++++++++++++++++++++
 1 file changed, 29 insertions(+)
 create mode 100644 rushs/data-clash/step-3/napoleon_connections.sql

(limited to 'rushs/data-clash/step-3/napoleon_connections.sql')

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
-- 
cgit v1.2.3

