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
