firstly, I am not a db programmer. I ply my trade on WordPress stuff, but not in-depth db structuring from scratch. please keep that in mind as I do my best to ask this question? :rolleyes:
I have made some headway creating two tables that I think will almost, kinda
do what I want, which is:
This is a (very lengthy) outline I've created in html that simply shows (above the image):
) » Barnes (supervised
) » Moore (supervised
) » de Mohrenschildt (knows
) » Oswald ...
AS WELL AS, Phillips (knows
) » Hunt (knows
) » Sturgis (followed, who also knows
) » Moore (same, pid3) ...
I put Sturgis followed by Moore specifically to show that these relationships are in no way numerically sequential in ASSOCS. There will be 4 or 500 Persons, (and 40 or 50 Organizations, once I get this solved).
So I have these tables:
4 de Mohrenschildt
And this query:
SELECT a1.p_id, p1.name AS 'Name', a2.p_id, p2.name AS 'FName1', a3.p_id, p3.name AS 'FName2'
FROM assocs a1
JOIN assocs a2 ON a1.p_id = a2.a_id
JOIN assocs a3 ON a2.p_id = a3.a_id
JOIN persons p1 ON a1.p_id = p1.id
JOIN persons p2 ON a2.p_id = p2.id
JOIN persons p3 ON a3.p_id = p3.id
WHERE p1.id = 1 AND p2.id = 2 AND p3.id = 3;
1 Phillips 2 Barnes 3 Moore
*** The problem I have is where a trail ends, like Phillips to Oswald, and starts again, Phillips to Moore, for instance...
*** What I need is a way to define an end to a string of associations and to start the next one, perhaps with another field or two in the ASSOCS table, or another table... (I'd also love to be able to denote one of a few types of relationships, i.e. "friend" "foe" "supervised" "worked for" ...)
*** I'm hoping some kind soul can help me with a query that can do this, and some advice on how to handle it in the tables I've started with...?