Relationship between two members

[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:
[table][tr]
[td][table][tr][td][b]ID[/b][/td][/tr][tr][td]type: int[/td][/tr][/table][/td]
[td][table][tr][td][b]RELID1[/b][/td][/tr][tr][td]type: int, is the user id that initiated the relationship request.[/td][/tr][/table][/td]
[td][table][tr][td][b]RELID2[/b][/td][/tr][tr][td]type: int, is the user id of the second person in the relationship.[/td][/tr][/table][/td]
[td][table][tr][td][b]Story[/b][/td][/tr][tr][td]type: varchar(255), quick blerb on how they are related.[/td][/tr][/table][/td]
[td][table][tr][td][b]Type[/b][/td][/tr][tr][td]type: int, a number 1-20 based on the relationship they have[/td][/tr][/table][/td]
[td][table][tr][td][b]Status[/b][/td][/tr][tr][td]type: int, 1 = confirmed by second person, 0 = not confirmed [/td][/tr][/table][/td][/tr][/table]

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

[b]Example Data:[/b]
[table][tr]
[td][table][tr][td][b]ID[/b][/td][/tr][tr][td]1[/td][/tr][tr][td]2[/td][/tr][tr][td]3[/td][/tr][tr][td]4[/td][/tr][/table][/td]
[td][table][tr][td][b]RELID1[/b][/td][/tr][tr][td]25[/td][/tr][tr][td]15[/td][/tr][tr][td]70[/td][/tr][tr][td]12[/td][/tr][/table][/td]
[td][table][tr][td][b]RELID2[/b][/td][/tr][tr][td]54[/td][/tr][tr][td]70[/td][/tr][tr][td]13[/td][/tr][tr][td]8[/td][/tr][/table][/td]
[td][table][tr][td][b]Story[/b][/td][/tr][tr][td]Met on the east coast[/td][/tr][tr][td]Met at walmart[/td][/tr][tr][td]Met walking[/td][/tr][tr][td]Met outside[/td][/tr][/table][/td]
[td][table][tr][td][b]Type[/b][/td][/tr][tr][td]14[/td][/tr][tr][td]11[/td][/tr][tr][td]3[/td][/tr][tr][td]8[/td][/tr][/table][/td]
[td][table][tr][td][b]Status[/b][/td][/tr][tr][td]1[/td][/tr][tr][td]1[/td][/tr][tr][td]1[/td][/tr][tr][td]1[/td][/tr][/table][/td][/tr][/table]

[b]Example Output:[/b]
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".
[i]So:[/i]
15 = Bob Smith
13 = Jane DOe

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

Thanks!

[fenway]

So you want to pull in both relids from the users table for each record in this table?

[phive_]

Yes, I need to pull both of them as either could contain the users ID (depending on who initiated the relationship).

[fenway]

Then you should just be able to join in the right table as desired.

[phive_]

Hum, I've looked into the join command, but to be honest I'm just a web designer and our database guy is on vacation until next week. So I do have some experience with SQL but with something like this I wouldn't know where to start.

SOO LOST  :-[

[phive_]

ok, after some googling i came up with this statement:

[code]
SELECT RELID1, RELID2, FirstName, LastName
FROM Relationship
INNER JOIN ReMembers
ON Relationship.RELID1 = ReMembers.AccountID
WHERE RELID1 = 69 OR RELID2 = 69
[/code]

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

Any help anyone can give, would be GREATLY appreciated... And will save me from pulling out all of my hair!

:-[

[phive_]

RESOLVED!!
[url=http://forums.devshed.com/ms-sql-development-95/arg--relationship-between-two-members-408574.html]http://forums.devshed.com/ms-sql-development-95/arg--relationship-between-two-members-408574.html[/url]

[fenway]

You were 99% there.