I have three tables, the relevant parts of which are:

users

users.userID, users.userNickname

groups

groups.groupID, groups.groupOwner, groups.groupName

mapFriendsGroups

mapFriendsGroups.userID, mapFriendsGroups.groupID

To be clear, the functionality is that a primary user can create new groups which appear in the groups table. A primary user has a list of friends (secondary users, each with their own entry in the users table) that they can add to one of their groups; this is represented by that friend's users.userID value being entered into the mapFriendsGroups.userID field of the mapFriendsGroups table. I then wrote this SQL to get the data that I want to display:

 SELECT `groups`.*, `users`.* FROM `groups` JOIN `mapFriendsGroups` ON `groups`.`groupID` = `mapFriendsGroups`.`groupID` JOIN `users` ON `users`.`userID` = `mapFriendsGroups`.`userID` WHERE `groups`.`groupOwner` = ? ORDER BY `groups`.`groupID`, `users`.`userLastName`

I handle this as a prepared statement in PHP and bind the primary user's users.userID value (which is held in a session variable) to the ?

This very nearly works but I discovered one problem - if a primary user creates a group but has not assigned any members to that group, the group will not be part of the results. I am convinced I'm doing a JOIN wrong but can't seem to get it to work.

I checked several other questions on stackoverflow (e.g. Select all users that belong to a certain group, complicated query and SQL: query get all users for a specific group and each user with all groups ) but all of those answers seem to assume that the groups are not restricted to a subset and filtered by the current user's ID.

Anyone have any suggestions?

In case it matters, here's what I'm using to parse the data after I get it:

if ($r->num_rows > 0) { // If it ran OK.
    $data1 = $r->fetch_assoc(); 
    $myGroups = '';
    $currentGroup = '';
    do {  
        // check the value of the current group variable against the actual current group name
        if ($currentGroup == $data1['groupName']){
            // this is part of a prior group...
            $myGroups .= ', '.$data1['userFirstName'].' '.$data1['userLastName'];
        } else {
            // this is not part of a prior group. check if the current group variable is empty - if it is then just start a new row
            if ($currentGroup == ''){
                $myGroups .= '<tr>';
                $myGroups .= '<td><strong>'.$data1['groupName'].'</strong></td>';
                $myGroups .= '<td>'.$data1['userFirstName'].' '.$data1['userLastName'];             
            } else {
                // close the prior group then start a new row
                $myGroups .= '</td>';
                $myGroups .= '</tr>';
                $myGroups .= '<tr>';
                $myGroups .= '<td><strong>'.$data1['groupName'].'</strong></td>';
                $myGroups .= '<td>'.$data1['userFirstName'].' '.$data1['userLastName']; 
            }
        }
        // assign the current group name to the current group variable
        $currentGroup = $data1['groupName'];
    } while($data1 = mysqli_fetch_assoc($r));
    // close the final row
    $myGroups .= '</td></tr>';

It ain't pretty, but it outputs a table of data where the users sees all of his groups (that have members in them) and who are the members (note that the creator of a group is NOT part of the group - this is important for later functionality). Again, I just need a tweak that will let this show empty groups. It's not an error in the PHP code - I've tested the SQL directly in phpMyAdmin's SQL tool and even there I'm not seeing the groups with zero members.

I wish I could just add the creator of the group to the group - that would enable my current code to do the job - but again that's not an option for this project.

EDIT 01 Here's a sample of the data:

from the users table userID = 5 is the primary user (the value of ? in the test query) other userID values are 1,2,3,4,8,10,11,12

from the groups table, only those groups whose groups.groupOwner value = 5

groupID = 11 | groupName = BFFs
groupID = 12 | groupName = Car
groupID = 13 | groupName = Shopping
groupID = 14 | groupName = Travel
groupID = 15 | groupName = Test
groupID = 18 | groupName = Gamers

from the mapFriendsGroups table, the relevant rows

userID = 1 | groupID 11
userID = 2 | groupID 11
userID = 3 | groupID 11
userID = 1 | groupID 12
userID = 2 | groupID 13
userID = 3 | groupID 13
userID = 1 | groupID 14
userID = 3 | groupID 14
userID = 1 | groupID 15
userID = 2 | groupID 15

EDIT 02 I took the csv export option from phpMySQL and dumped it into a text editor, removed the irrelevant columns, and am pasting it here. Note there's 10 rows.

11,5,BFFs,3,Alex,Smith
11,5,BFFs,2,Ben,Smith
11,5,BFFs,1,Allison,Smith
12,5,Car,1,Allison,Smith
13,5,Shopping,3,Alex,Smith
13,5,Shopping,2,Ben,Smith
14,5,Travel,3,Alex,Smith
14,5,Travel,1,Allison,Smith
15,5,Test,2,Ben,Smith
15,5,Test,1,Allison,Smith

I'm trying to get this same type of output, but with an 11th row that would be:

18,5,Gamers,,,

That is, the group that has no members in it.

EDIT 03 - FINAL EDIT I've got it working. The trick seems to be to make both of the joins into LEFT JOIN - that gives me the needed output of 11 rows, i.e. 10 rows with associated members of a group and 1 row for a group that has no members (and which has null values for the fields of userID and userName so I'll need to handle that in the PHP code that parses it... but that's trivial).

Thank you very much to all of you that contributed. I will mark as the correct answer the contribution which helped me the most to find the correct answer.

CodePudding user response：

You have not a relation between groups and mapFriendsGroups you should use LEFT JOIN ( and not JOIN - INNER JOIN ) for manage this case

SELECT `groups`.*, `users`.* 
FROM `groups` 
LEFT JOIN `mapFriendsGroups` ON `groups`.`groupID` = `mapFriendsGroups`.`groupID` 
LEFT JOIN `users` ON `users`.`userID` = `mapFriendsGroups`.`userID` 
WHERE `groups`.`groupOwner` = ? 
ORDER BY `groups`.`groupID`, `users`.`userLastName`

CodePudding user response：

You should use left join from the "groups" table to "mapFriendsGroups" table. That way if there is no record in the "mapFriendsGroups" you still can retrieve record from "groups" table. Here is the query:

SELECT groups.*, users.*
FROM groups
LEFT JOIN mapFriendsGroups ON groups.groupID = mapFriendsGroups.groupID
INNER JOIN users ON users.userID = mapFriendsGroups.userID
WHERE groups.groupOwner = ?
ORDER BY groups.groupID, users.userLastName