1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

some significant query help, please

Discussion in 'MySQL' started by glennnall, Sep 24, 2016.

  1. #1
    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 two tables that I think will almost, kinda do what I want, which is an outline like this, only MUCH bigger:
    • Phillips pid1
      • Barnes pid2
        • Moore pid3
          • de Mohrenschildt pid4
            • Oswald pid5
      • Hunt pid6
        • Sturgis pid7
          • Moore pid3
    This is a (very lengthy) outline I've created in html that simply shows:
    SEMrush
    Phillips (knows) » Barnes (supervised) » Moore (supervised) » de Mohrenschildt (knows) » Oswald ...

    AS WELL AS, Phillips (knows) » Hunt (knows) » Sturgis (followed, who also knows) » Moore (same one, pid3) ...

    I put Sturgis followed by Moore specifically to show that these relationships are in no way numerically sequential. There will be 4 or 5 hundred Persons, (and 40 or 50 Organizations, once I get this solved).

    You can see an example of it here (there's lots of data going on in this outline):
    http://stemmonsfreeway.com/military-industrial-intelligence-anti-castro-syndicated/

    So I have these tables:
    
        [persons]
        id, name             
    -----------------
        1   Phillips
        2   Barnes
        3   Moore
        4   de Mohrenschildt
        5   Oswald
        6   Hunt
        7   Sturgis
    
        [assocs]
       p_id, a_id
    ----------------
        1     2
        2     3
        3     4
        4     5
        1     6
        6     7
        7     3
    
    Code (markup):
    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;
    
    Code (markup):
    which returns:

    1 Phillips 2 Barnes 3 Moore

    *** The problem I have is where a branch ends - Phillips to Oswald - and another starts again - Phillips to Moore, for instance.

    *** What I need is a way to define an end to a string of associations and a start of 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...?

    db1.jpg
     
    glennnall, Sep 24, 2016 IP
    SEMrush
  2. Einheijar

    Einheijar Well-Known Member

    Messages:
    537
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    115
    #2
    Perhaps creating a sqlfiddle will help people solve your problems. The easiest way is probably to run the logic in PHP, you can also add in relationships to the assocs table
     
    Einheijar, Oct 7, 2016 IP