Relationship between two members

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Relationship between two members

Author

Topic

phive
Starting Member

3 Posts

Posted - 2006-12-09 : 00:30:15

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

What I've Tried:


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 ~

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-12-09 : 01:42:24

This is not the proper way to setup this type of database, however you can make it work.
select Distinct a.* from (
SELECT RELID1, RELID2, FirstName, LastName
FROM Relationship
INNER JOIN ReMembers
ON Relationship.RELID1 = ReMembers.AccountID
union all
SELECT RELID1, RELID2, FirstName, LastName
FROM Relationship
INNER JOIN ReMembers
ON Relationship.RELID2 = ReMembers.AccountID) a
WHERE a.RELID1 = 70 OR a.RELID2 = 70

phive
Starting Member

3 Posts

Posted - 2006-12-09 : 02:11:05

Thank you so much!

Its funny that you say that, see our database guy is on vacation until next Monday and I'm just the the web developer. As fate would have it, we have to do a presentation on Monday so I'll be working this weekend getting a few things working.

My next question I guess would be, how would you suggest setting this up?

Two tables? or have it in as a string in the ReMembers table?

At this point any help I can get is MORE then appreciated!

phive
Starting Member

3 Posts

Posted - 2006-12-09 : 13:04:48

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 17:05:32

Thank you for occupying more people than necessary for solving this matter!

-- prepare test data
declare @test table (ID int, RELID1 int, RELID2 int, Story varchar(255), Type int, Status int)

insert	@test
select	1, 25, 54, 'Met on the east coast', 14, 1 union all
select	2, 15, 70, 'Met at walmart', 11, 1 union all
select	3, 70, 13, 'Met walking', 3, 1 union all
select	4, 12, 8, 'Met outside', 8, 1

-- do the work
select		u.*
from		(
			select	relid1 relid
			from	@test
			where	relid2 = 70
				and relid1 <> relid2
			union
			select	relid2
			from	@test
			where	relid1 = 70
				and relid2 <> relid1
		) q
inner join	users u on u.unqid = q.relid

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 17:45:43

You should also take a look at this topic,
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=72097

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources