Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    4

    Unhappy Unanswered: Table Setup: How two members are related

    Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain...

    I set up a table called relationships that looks like this:

    ID
    type: int

    RELID1
    type: int, is the user id that initiated the relationship request.

    RELID2
    type: int, is the user id of the second person in the relationship.

    Story
    type: varchar(255), quick blerb on how they are related.

    Type
    type: int, a number 1-20 based on the relationship they have

    Status
    type: int, 1 = confirmed by second person, 0 = not confirmed

    What i'd like to have pulled is a list of the people that person (for example: 70) is related to. I am having the two following problems:
    1. unqid of "70" could be in RELID1 or RELID2 as they could have initiated the request or been the second person.
    2. I don't want to display them selves in their own relationship listing

    Example Data:
    ID
    1
    2
    3
    4

    RELID1
    25
    15
    70
    12

    RELID2
    54
    70
    13
    8

    Story
    Met on the east coast
    Met at walmart
    Met walking
    Met outside

    Type
    14
    11
    3
    8

    Status
    1
    1
    1
    1

    Example Output:
    Again assuming the current user is "70", the sql should pull: 15, 13.. but i'd like to pull their names from another table called "Users" where "15" and "13" are the UnqID's in a column called "ID".
    So:
    15 = Bob Smith
    13 = Jane Doe

    The following code works great that I got with the help on another form:

    Code:
    SELECT Relationship.RELID1
         , Relationship.RELID2
         , ReMembers.FirstName
         , ReMembers.LastName
      FROM Relationship
    INNER 
      JOIN ReMembers 
        ON ReMembers.AccountID = Relationship.RELID1
     WHERE Relationship.RELID2 = 70
    UNION ALL
    SELECT Relationship.RELID1
         , Relationship.RELID2
         , ReMembers.FirstName
         , ReMembers.LastName
      FROM Relationship
    INNER 
      JOIN ReMembers 
        ON ReMembers.AccountID = Relationship.RELID2
     WHERE Relationship.RELID2 = 70
    My question is: What is the best way to set this table up? I'm not committed to any any design as of right now, but want to be sure I set it up in the most efficient manner.

    Any feedback / opinions are welcome!
    Last edited by phive_; 12-09-06 at 13:12.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the best way to set this table up is exactly how you've got it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    Yep, keep it that way. Nicely done. ... but I think the first where clause should say
    WHERE Relationship.RELID1 = 70 ... minor typo ...

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Yveau, you are right, what a great eye!!

    i am blushing because that's my code, isn;t it phive?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73

    Just doing my little part of helping each other out ...

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  6. #6
    Join Date
    Mar 2005
    Posts
    4

    Talking :-)

    Quote Originally Posted by r937
    Yveau, you are right, what a great eye!!

    i am blushing because that's my code, isn;t it phive?
    IT SURE IS!! Thank you again so much, my DB guy came back yesterday and was floored that I was able to put together the website with table and relationships in place the way I did.

    He knew very well that I don't know SQL, so I showed him our other post and he was very impressed with your work. First time in a long time he didn't have "another way" of doing it.

    Thanks again r937, your a credit to your species! lol

    ~ Phive

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •