CODEWITHSUNDEEP
mysqlgroup-by
Drew
Drew

Reputation: 6274

query design with grouping

I'm having trouble coming up with a working query for this situation...

Table:

[ matchID ]  [ User ] [ team ]
     1          10       1
     1          77       2
     2          10       1
     2          77       1
     3          10       2
     4          10       1

Notes:

Desired Query Results:

A list of matchIDs for a given user where the opponent is unknown.

My first attempt was:

SELECT matchID FROM table
GROUP BY matchID
HAVING COUNT(matchID) = 1

but then I realized that I wasn't counting matches where there are teammates but unknown opponents, such as match 2 above.

In plain English, I need to get all matchIDs where:

but I'm not sure how to do that efficiently in one query. Help?

Upvotes: 1

Views: 57

Answers (3)

davogotland
davogotland

Reputation: 2777

you haven't provided a table name, so i have decided to call it mut as in short for matches users teams. anyways, this seems to be working on all cases:

SELECT DISTINCT
    `matchID`
FROM
    `mut`
WHERE
    `user`=10
AND
    `matchID` NOT IN (
        SELECT
            `m1`.`matchID`
        FROM
            `mut` AS `m1`
        INNER JOIN
            `mut` AS `m2`
        ON
            `m1`.`matchID`=`m2`.`matchID`
        AND
            `m1`.`team`<>`m2`.`team`
    )

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

SELECT MatchID
  FROM Table
 GROUP BY MatchID
HAVING COUNT(DISTINCT Team) = 1;

You seem to need those matches where only one team is recorded in the table, and this query gives you those matches.

If the match must involve a specific user (10 for the sample data), then:

SELECT t1.MatchID
  FROM Table AS t1
 WHERE EXISTS(SELECT *
                FROM Table AS t2
               WHERE t2.UserID = 10 AND t1.MatchID = t2.MatchID)
 GROUP BY t1.MatchID
HAVING COUNT(DISTINCT t1.Team) = 1;

You can probably do that as an (inner) self-join:

SELECT t1.MatchID
  FROM Table AS t1
  JOIN Table AS t2 ON t1.MatchID = t2.MatchID AND t2.UserID = 10
 GROUP BY t1.MatchID
HAVING COUNT(DISTINCT t1.Team) = 1;

Or, perhaps a little more clearly:

SELECT t1.MatchID
  FROM Table AS t1
  JOIN (SELECT MatchID FROM Table AS t2 WHERE t2.UserID = 10) AS t2
    ON t1.MatchID = t2.MatchID
 GROUP BY t1.MatchID
HAVING COUNT(DISTINCT t1.Team) = 1;

The sub-select picks those matches where UserID 10 played; the rest works as before.

Upvotes: 2

mathematical.coffee
mathematical.coffee

Reputation: 56905

You can count the number of teams per match. If there's only one then there's not enough:

SELECT matchID 
FROM table
GROUP BY matchID 
HAVING COUNT(DISTINCT(team))=1;

Upvotes: 1

Related Questions