phive_ Posted December 9, 2006 Share Posted December 9, 2006 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 Smith13 = Jane DOeIf you have any more questions, or something isn't clear please let me know!Thanks! Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/ Share on other sites More sharing options...
fenway Posted December 9, 2006 Share Posted December 9, 2006 So you want to pull in both relids from the users table for each record in this table? Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-137884 Share on other sites More sharing options...
phive_ Posted December 9, 2006 Author Share Posted December 9, 2006 Yes, I need to pull both of them as either could contain the users ID (depending on who initiated the relationship). Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-137896 Share on other sites More sharing options...
fenway Posted December 9, 2006 Share Posted December 9, 2006 Then you should just be able to join in the right table as desired. Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-137898 Share on other sites More sharing options...
phive_ Posted December 9, 2006 Author Share Posted December 9, 2006 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 :-[ Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-137909 Share on other sites More sharing options...
phive_ Posted December 9, 2006 Author Share Posted December 9, 2006 ok, after some googling i came up with this statement:[code]SELECT RELID1, RELID2, FirstName, LastNameFROM RelationshipINNER JOIN ReMembersON Relationship.RELID1 = ReMembers.AccountIDWHERE 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! :-[ Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-137924 Share on other sites More sharing options...
phive_ Posted December 9, 2006 Author Share Posted December 9, 2006 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] Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-138108 Share on other sites More sharing options...
fenway Posted December 15, 2006 Share Posted December 15, 2006 You were 99% there. Link to comment https://forums.phpfreaks.com/topic/29999-relationship-between-two-members/#findComment-142074 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.