MySQL

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



Go Back   Tutorialized ForumsDatabasesMySQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
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

URL

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:

Code:
    [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


And this query:
Code:
    
    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;


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
Reply

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 - 2018, Jelsoft Enterprises Ltd.

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