MySQL query two tables in one statement

Question

I have a table called, group, and another table called member. group has fields of groupId, name, etc... member has fields of memberId, groupId, memberName, etc... where groupId is being a foreign key to group table. I want to query these two table in a single statement such that the result outputs as follows.

[
  {
    "groupId": "8",
    "name": "Test Group 2",
    "status": "0",
    "member": [
      {
        "memberId": "1",
        "groupId": "8",
        "memberName": "tester 1"
      }
    ]
  }
]

I have looked into INNER JOIN and UNION but couldn't figure out how to achieve this. Any advice would be helpful. Thank you.

`SELECT * FROM group INNER JOIN member ON group.groupId = member.groupId` — Oleg Nurutdinov, Nov 19 '18 at 09:12
Possible duplicate https://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables — pc_, Nov 19 '18 at 09:16
@pc_ This question indeed looks like something that already has an answer. But the mentioned duplicate does not seem to fit in the scope of this question IMO. — Koen Hollander, Nov 19 '18 at 09:18
@Koen Hollander Thanks, looks to me that both issues are almost identicall, I myself could work out the answer on the one I pointed from answers there. — pc_, Nov 19 '18 at 09:21
@pc_ I see, they indeed look the same, maybe the scope of the questions does not fit, but the answers does. Thanks! (But still, I don't think that the mentioned question will solve this question also. But, that's my opinion ;) ) — Koen Hollander, Nov 19 '18 at 09:22

score 0 · Answer 1 · answered Nov 19 '18 at 09:13

You can use a join

    SELECT * 
    FROM group 
    INNER JOIN member ON group.groupId = member.groupId

but the result is as

    [
      {
        "groupId": "8",
        "name": "Test Group 2",
        "status": "0",
        "memberId": "1",
         "groupId": "8",
        "memberName": "tester 1"

      }
    ]

a row for each join between master and detail rows

SQL query return tabular result and not nested result

Koen Hollander · Answer 2 · 2018-11-19T09:19:57.070

Your question is broad, but maybe this will help you

SELECT user.memberName, group.name
FROM user 
INNER JOIN group ON group.groupId = user.groupId

Not tested, but this will select the group name and username from the tables with an INNER JOIN

You can also select other columns, but be sure that they don't have the same name. Otherwise the select will fail. (You can work that around by using the as keyword mentioned here)

score 0 · Accepted Answer · answered Nov 19 '18 at 10:18

You can do it with 2 queries, array column, and array map.

First query, get all the needed groups, and fetch it into a variable e.g $groups

SELECT group_id, name, status FROM groups

And then get all group_id from the $groups.

$groupIds = implode(',', array_column($groups, 'group_id'));

You will get list of group_id as a string separated with comma. And then use it in second query:

"SELECT member_id, group_id, member_name FROM member WHERE group_id IN (" . $groupIds . ")"

Fetch the result in $members. And the final task is to combine the result.

foreach ($groups as $key => $group) {
    $groupMember = array_filter($members, function($member) use ($group) {
        return $group['group_id'] == $member['group_id'];
    });
    $group['member'] = $groupMember; // I don't know if this works
    $groups[$key] = $groupMember; // if previous line doesn't work
}

P.S This will be a lot easier if you use PHP Framework like Laravel. It has eloquent one to many and eager load with method which you can achieve same result with just:

Group::with("member")->get();

I don't know other framework, I'm sure there are other frameworks that are able to do this too.

This works. Since I wanted to have a member array within every each group, I ended up doing what you wrote. I guess this is not possible to do by one query. Thanks! — KoreanDude, Nov 21 '18 at 01:24

MySQL query two tables in one statement

3 Answers3