Relationship between two members - HELP!

[Phive]

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...

The table (relationships) 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

[b]What I've Tried:[b]

SELECT RELID1, RELID2, FirstName, LastName
FROM Relationship
INNER JOIN ReMembers
ON Relationship.RELID1 = ReMembers.AccountID
WHERE RELID1 = 70 OR RELID2 = 70
 

This works, however, this only works for RELID1 and not RELID2 column. Moreover, this repeats the data for some reason.


If you have any more questions, or something isn't clear please let me know!

THANKS!

~ PhIve ~

[almaz]

[b]What I've Tried:[b]
...
This works, however, this only works for RELID1 and not RELID2 column. Moreover, this repeats the data for some reason.

Your query is correct, you just don't select the second member's name:

SELECT RELID1, RELID2, R1.FirstName, R1.LastName, R2.FirstName, R2.LastName
FROM Relationship
INNER JOIN ReMembers R1 ON Relationship.RELID1 = R1.AccountID
INNER JOIN ReMembers R2 ON Relationship.RELID2 = R2.AccountID
WHERE RELID1 = 70 OR RELID2 = 70