CODEWITHSUNDEEP
phpmysql
rjmcb
rjmcb

Reputation: 3745

Check if group exists mysql query

This is my table structure

groupings

id groupName 
1  group1
2  group2
3  group3
4  andSoOn

group_members(correct)

groupingId accountId groupLeader
1          5001      5001
1          5002      5001     
2          5001      5001
2          5002      5001
2          5003      5001
3          5001      5001
3          5002      5001
3          5003      5001
3          5004      5001

here's my problem, every group should be unique, in a way that the members should not be the same in a group...

for example :

group_members(wrong)

groupingId accountId groupLeader
1          5001      5001
1          5002      5001     
2          5001      5001
2          5002      5001
2          5003      5001
3          5001      5001
3          5002      5001
3          5003      5001

an insert statement with the groupingId = 3 will fail because 5001,5002,5003 already exists on groupingId = 2

what should be my select statement to check if the members already exists in a group... btw, im using PHP for this

Upvotes: 1

Views: 559

Answers (2)

liquorvicar
liquorvicar

Reputation: 6106

You need something like this

SELECT groupingId, COUNT(*) AS accounts_present
FROM group_members
WHERE accountId IN (5001,5002,5003)
GROUP BY groupingId
HAVING accounts_present=3 // 3 here is the number of accounts in the "group", i.e. in the IN clause

This will return all groupingIds that have all the relevant accountIds. If it returns nothing you can be sure it is not a duplicate.

Upvotes: 2

Jarosław Gomułka
Jarosław Gomułka

Reputation: 4995

This query gives you unique string for every different group 

SELECT GROUP_CONCAT(CONCAT(accountId, ',', groupLeader) SEPARATOR '|')  FROM group_members GROUP BY groupingId;

So this will give you number of different groups 

SELECT count(*) FROM (SELECT DISTINCT GROUP_CONCAT(CONCAT(accountId, ',', groupLeader) SEPARATOR '|') AS unique_string FROM group_members GROUP BY groupingId) AS tmp_table;

Now you need to compare it with number of groups.

Upvotes: 1

Related Questions