2

Using MySqL 5.7.34 and having a table, groups_members, with 4 columns

ID group member organization
1 A Bob Apple
2 A Susan Apple
3 A Joe Apple
4 B Steve Microsoft
5 B Mike Microsoft
6 B Bob Microsoft
7 C Susan Apple
8 C Bob Apple
9 C Joe Apple

I want to select all groups that have BOTH Bob AND Joe as members and the organization is Apple only, with each group being just one row in the results.

Expected results:

group
A
C

If it is even possible, I'd ideally want to also select and sum some values for each group from another table in the same query. Basically get all groups with Bob and Joe as members and get that group's combined points and miles:

ID group points miles
1 A 10 100
1 C 10 150
1 A 10 100
1 B 10 200
1 B 10 100

So the IDEAL expected results would actually be:

group points miles
A 20 200
C 10 150

I can think of some ways to do this with multiple queries and some app logic. But it'd be great if it was possible to do all with one query.

1
  • Good questions. MySQL version 5.7.34 And yes, the organization should be Apple only. Aug 31, 2022 at 19:10

2 Answers 2

0

One possible way:

select gm.`group`,SUM(at.points) as points,SUM(at.miles) as miles
from another_table at 
inner join (select `group` 
            from groups_members
            where member in ('Bob','Joe')
            group by `group`
            having count(distinct member) >=2 
            ) as gm on at.`group`=gm.`group`
where not exists (select 1 
                 from groups_members g 
                 where gm.`group` = g.`group` 
                 and organization <> 'Apple')
group by gm.`group`;

Note. I changed the organization from (2,'A','Susan','Apple'), to (2,'A','Susan','Microsoft'), , just for testing purpose

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=af373d5f67a7f9918b6e441be95723d6

Please do not use MySQL Reserved Words such as group in your case. I suggest renaming if possible, otherwise you have to put it inside backticks.

The subquery return only the group which at least have ('Bob','Joe'), it might be others as well.

The where not exists will filter all the groups which have organization !='Apple'.

Below indexes should help the optimizer to speed up the queries

alter table `groups_members`  add index `gr_me` (`group`,`member`);
alter table `another_table `  add index `group` (`group`);
1
  • 1
    That works great. From the first thing you posted, on the first line, I just needed to change at.points to SUM(at.points), same with at.miles. And then group by gm.group at the bottom. But then it works perfectly. (That may be what you go on to do your edit, I haven't tried that out yet.) Thanks so much for your help, I've learned a lot. Aug 31, 2022 at 20:26
0

First query:

SELECT DISTINCT gm1.group
  FROM groups_members AS gm1
  JOIN groups_members AS gm2  ON gm2.group = gm1.group
                             AND gm2.member <> gm1.member
 WHERE gm1.member IN ("Bob","Joe")
   AND gm2.member IN ("Bob","Joe")
   AND gm1.organization = "Apple" 
   AND gm2.organization = "Apple"
;

Second query is a bit unclear, especially points and miles calculations for group C.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.