CODEWITHSUNDEEP
mysql
bluewave41
bluewave41

Reputation: 145

MYSQL requires group by but query produces wrong results with it

Given a table that looks like this

reportID username
-----------------
1      | a
1      | b
1      | c
2      | a
3      | c
3      | a

Given a query that takes in 2 or more usernames it should return the rows which contain both usernames and a matching reportID.

For example if given a and b it would return

reportID username
-----------------
1      | a
1      | b

Since they share a reportID.

SELECT reportID, username 
FROM players 
WHERE username IN('a', 'b') 
group by reportID, username
having count(reportID) > 1

Is as close as I can get. Adding the group by username ruins the query and returns 0 results but it's required as without it this error is produced:

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'x.x.username' which is not functionally dependent on 
columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by

What can I do to make this work?

Upvotes: 1

Views: 131

Answers (2)

sk11z00
sk11z00

Reputation: 124

Try this query:

SELECT reportID, username 
FROM players 
WHERE reportID in (
  select reportID
     from players where username IN('a', 'b') 
     group by reportID
     having count(reportID) > 1
)

Upvotes: 1

Léo R.
Léo R.

Reputation: 2708

Having count is useless just do this :

SELECT * FROM players WHERE username IN ('a', 'b') group by username

Upvotes: 0

Related Questions