I don't know MySQL very well, but you can do this in MSSQL (and it might
work in MySQL too):
<cfquery name="list_member_friends_a" datasource="members">
SELECT cc.first_name as first_name, cc.last_name as last_name
FROM tbl_member_core_contact cc
INNER JOIN tbl_member_friend_list fl ON cc.member_id =
fl.fmember_id_b
WHERE (member_id_a = <cfqueryparam value="#myid#">)
UNION ALL
SELECT cc.first_name as first_name, cc.last_name as last_name
FROM tbl_member_core_contact cc
INNER JOIN tbl_member_friend_list fl ON cc.member_id =
fl.fmember_id_a
WHERE (member_id_b = <cfqueryparam value="#myid#">)
</cfquery>
HTH,
Carl
On 4/8/2011 10:55 AM, Jason King wrote:
Thanks guys. Lots of good advice.
I'm going to keep it simple and use a 2 column table.
member1 (int)
member2 (int)
The member who initiates the request would be member1 and their friend
would be member2
In the way I'm looking at coding this, there will be a single entry
for a connection between people.
So when a user's friend list is generated, I will need to query where
member_id = member1 OR member_id = member2
This is because your member_id could be on either side depending on if
the connection is one you initiated or the other member initiated.
In order to build the final friend's list, I'm going to do 3 queries.
First, I will query the friends table where member1 = member_id
Second, I will query the friends table where member2 = member_id
In both queries, the friend's id will be queries as friend_id
<cfquery name="list_member_friends_a" datasource="members">
SELECT member_id_b as friend_id
FROM tbl_member_friend_list
WHERE (member_id_a = <cfqueryparam value="#myid#">)
</cfquery>
<cfquery name="list_member_friends_b" datasource="members">
SELECT member_id_a as friend_id
FROM tbl_member_friend_list
WHERE (member_id_b = <cfqueryparam value="#myid#">)
</cfquery>
After this, I will union the two
<cfquery name="combine_friend_list" dbtype="query">
SELECT listA.friend_id
FROM list_member_friends_a
UNION ALL
SELECT listB.friend_id
FROM list_member_friends_b
</cfquery>
Then, now that I have a list of friend id's, I need to grab each
friend's first and last name and create the final query that will be
used to present the list.
<cfquery name="get_friend_names" datasource="members">
SELECT cc.first_name as first_name, cc.last_name as last_name
FROM tbl_member_core_contact as cc
INNER JOIN combine_friend_list as fl on cc.member_id = fl.friend_id
</cfquery>
Everything works except for the last query. It's not letting me do an
inner join using the previous query as a table.. Ideas?
--
official tag/function reference: http://openbd.org/manual/
mailing list - http://groups.google.com/group/openbd?hl=en
--
official tag/function reference: http://openbd.org/manual/
mailing list - http://groups.google.com/group/openbd?hl=en
