Gathering Data Based on a "friends" status

[deth4uall]

Hi I have these tables roughly that have info in them but I am trying to write a SQL query that will gather them all together in one query...

 

                // User table
	// -------------------------------------
	// userid	username	lastactivity
	// -------------------------------------
	// 1		abc			1244912985
	// 2		def			1244912983
	//
	// Friends table
	// -------------------------------------
	// userid	relationid	friend
	// -------------------------------------
	// 1		2			yes
	// 2		1			yes

I am not versed with the Outer Joins so I would like to see an example of what it would look like to gather info based on your `id` being '1', and both of the above friends table being set to yes. Also I need pretty much all the info in the User table, based on the Friends table (both ids being friends with one another).

[kickstart]

Hi

 

Not exactly sure what you are after. I presume what you want is a list of friends for a particular id. So with your example data you would get 1 as the id and 2 as the friend (and possibly with more data 3, 4, 5, etc also as friends).

 

If so, something like this would do it:-

 

SELECT a.userid, a.username, c.userid, c.username
FROM UserTable a
LEFT OUTER JOIN FriendsTable
ON a.userid = b.userid
AND friend = 'yes'
LEFT OUTER JOIN UserTable c
ON b.relationid = c.userid
WHERE a.userid = 1

 

All the best

 

Keith

 

[deth4uall]

ok thanks I will try that and see if it works...