Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Remember me

Go Back   Tutorialized ForumsDatabasesMySQL

Add This Thread To:   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
Unread Tutorialized Forums Sponsor:
Old September 24th, 2016, 08:00 PM
glennnall glennnall is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
Join Date: Sep 2016
Posts: 1 glennnall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 m 7 sec
Reputation Power: 0
Some real Query help, please

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:
  • 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 (above the image):

Phillips (knows) 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:

    id, name              
    1   Phillips 
    2   Barnes
    3   Moore
    4   de Mohrenschildt
    5   Oswald
    6   Hunt
    7   Sturgis

   p_id, a_id
    1     2
    2     3
    3     4
    4     5
    1     6
    6     7
    7     3

And this query:
    SELECT a1.p_id, AS 'Name', a2.p_id, AS 'FName1', a3.p_id, 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 =
    JOIN persons p2 ON a2.p_id =
    JOIN persons p3 ON a3.p_id =
    WHERE = 1 AND = 2 AND = 3;

that returns:
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...?

Last edited by glennnall : September 24th, 2016 at 08:07 PM. Reason: trying to add an image, but it doesn't seem to add

Reply With Quote

Viewing: Tutorialized ForumsDatabasesMySQL > Some real Query help, please

Developer Shed Advertisers and Affiliates

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap