I need to retrieve data from a number of tables in a specific format. I have a query that retrieves this data for a list of users but I am having problems modifying it for my present purpose. First I will describe the tables, then I will describe the format, and finally I will show my attempt so far at the query, which does not work. (Much the same information is retrieved from the query listed at the bottom of this question, but I am having trouble extracting the relevant parts of that query for use in a different manner.)
The tables have the following relevant columns:
1) The 'users' table consists of columns for user_id and user_name (both unique), and first_name, last_name, and a region_id.
2) The 'groups' table consists of three columns, the first, 'member_id' being the user_id of a user, the second, 'group_id' being the group_id of a group to which this user belongs, and the third, nickname, being the nickname of the user within this group.
3) The 'group_stats' table consists of two columns, group_name and group_id.
4) The 'friends' table contains two columns, fan_id and star_id. The former column identifies the user_id of a user who has indicated that the user_id in the latter column is that of a friend.
5) The 'regions' table pairs region_id with region_name.
From these tables I need to retrieve data relevant to a user with a given user_name:
1) A concatenated list of the names of groups to which the user possessing the given user_name belongs (referencing the 'groups' table for the group relationships), followed by the nickname relevant to each group in parentheses (only if a nickname is not NULL).
The format should be like this:
group_name1 (group1nickname), group_name2, group_name3 (group3nickname)
2) A similar concatenated list of friends who share at least one mutual friend in common with the user possessing the given user_name. (A user x is a mutual friend of a user y if there is a row in the friends table in which user_id(x) is the fan_id and user_id(y) is the star_id and there is another row in which user_id(y) is the fan_id and user_id(x) is the star_id. (The results should exclude the case in which the user possessing the relevant user_name has a mutual friend in common with himself. The list should also not include an entry for the same friend more than once.)
The format should be like this:
'friend_first_name1 friend_last_name1, friend_first_name2 friend_last_name2, friend_first_name3 friend_last_name3'
3) The entire row of data for that user_name from the users table.
4) The region_name corresponding with the user_name (by way of the region_id in the users table and the corresponding region_name in the regions table)
My current query:
This works for (3) and (4):
SELECT users.*, region_name FROM users, regions WHERE user_name = '$profile_user_name' AND users.region_id=regions.re
gion_id
I tried adding (1) by transferring part of a related query (listed below), but I am doing something wrong. I want to fix this version and then include the necessary syntax to return the data required in (2):
SELECT users.*, region_name, group_q2.group_names
FROM users, regions
(
SELECT user_id, GROUP_CONCAT( group_name
SEPARATOR ', ' ) AS group_names
FROM (
SELECT g2.member_id AS user_id, CONCAT(gs.name, IFNULL(CONCAT(' (', g2.nickname, ')'), '')) AS group_name
FROM groups g1, groups g2, group_stats gs
WHERE g1.member_id =$profile_user_name
AND g1.group_id = g2.group_id
AND g2.member_id != g1.member_id
AND g1.group_id = gs.group_id
) AS group_q
GROUP BY user_id
) AS group_q2
WHERE user_name = '$profile_user_name' AND users.region_id=regions.re
gion_id
Here is the original (functional) query from which I tried to transplant the relevant section for the 'groups' data. There is an analogous relevant section for the 'friends' data. This query returns this and other relevant information for a list of users, rather than just one:
SELECT SQL_CALC_FOUND_ROWS friends_rollup . * , group_q2.group_names, common_q2.linked_names, users.last_name, users.first_name, users.last_siting_time, users.last_siting, users.email, users.user_name, NOW( )
FROM users, (
SELECT user_id, SUM( direct_count ) AS direct_count, SUM( group_count ) AS group_count, SUM( linked_count ) AS linked_count
FROM (
SELECT f2.star_id AS user_id, 1 AS direct_count, 0 AS group_count, 0 AS linked_count
FROM friends f2, friends f3
WHERE f2.fan_id ={$_SESSION['user_id']}
AND f2.star_id = f3.fan_id
AND f2.fan_id = f3.star_id
UNION ALL
SELECT g2.member_id AS user_id, 0 AS direct_count, 1 AS group_count, 0 AS linked_count
FROM groups g1, groups g2
WHERE g1.member_id ={$_SESSION['user_id']}
AND g1.group_id = g2.group_id
AND g2.member_id != g1.member_id
UNION ALL
SELECT f6.star_id AS user_id, 0 AS direct_count, 0 AS group_count, 1 AS linked_count
FROM friends f4, friends f5, friends f6, friends f7
WHERE f4.star_id ={$_SESSION['user_id']}
AND f7.star_id = f4.fan_id
AND f6.star_id = f5.fan_id
AND f6.fan_id = f5.star_id
AND f7.star_id = f5.star_id
AND f7.fan_id = f4.star_id
AND f6.star_id != f4.star_id
) AS friends_list
GROUP BY user_id
) AS friends_rollup
LEFT JOIN (
SELECT user_id, GROUP_CONCAT( group_name
SEPARATOR ', ' ) AS group_names
FROM (
SELECT g2.member_id AS user_id, CONCAT(gs.name, IFNULL(CONCAT(' (', g2.nickname, ')'), '')) AS group_name
FROM groups g1, groups g2, group_stats gs
WHERE g1.member_id ={$_SESSION['user_id']}
AND g1.group_id = g2.group_id
AND g2.member_id != g1.member_id
AND g1.group_id = gs.group_id
) AS group_q
GROUP BY user_id
) AS group_q2 ON ( friends_rollup.user_id = group_q2.user_id )
LEFT JOIN (
SELECT user_id, GROUP_CONCAT( linked_name
SEPARATOR ', ' ) AS linked_names
FROM (
SELECT f6.star_id AS user_id, CONCAT(u2.first_name, ' ', u2.last_name) AS linked_name
FROM friends f4, friends f5, friends f6, friends f7, users u2
WHERE f4.star_id ={$_SESSION['user_id']}
AND f7.star_id = f4.fan_id
AND f6.star_id = f5.fan_id
AND f6.fan_id = f5.star_id
AND f7.star_id = f5.star_id
AND f7.fan_id = f4.star_id
AND f6.star_id != f4.star_id
AND u2.user_id = f7.star_id
) AS common_q
GROUP BY user_id
) AS common_q2 ON ( friends_rollup.user_id = common_q2.user_id )
WHERE users.user_id = friends_rollup.user_id
ORDER BY direct_count DESC , linked_count DESC , group_count DESC
LIMIT $start, $display;
