How can I keep my records grouped together using SQL?

Suppose I have a GAME table with the following fields

match_id, user_id, result

The results always come in pairs with identical match_id. So for example

1837, 4, Win
1837, 29, Forfeit

I would like to separate into two groups, one group that has results and one group that doesn't. This is pretty easily done by using WHERE result <> ''

However I've noticed a few strange records such as the following

1839, 5, Win
1839, 40,

The second record does not have a result recorded against it. Therefore using result <> '' puts splits match 1839 into two separate groups whereas I want them to stay together. Can I achieve this in SQL?

So basically my pseudo code is:

if both have a result First group

If either have the result First group

If none have a result Second grou开发者_如何学运维p

Full code using MySQL

SQL1: SELECT * from GAME where result <> ''

Gives

1837, 4, Win
1837, 29, Forfeit
1839, 5, Win


SQL2: SELECT * from GAME where result = ''

1839, 40,
1850, 30,
1850, 5,

I'm looking for

SQL1: ??

1837, 4, Win
1837, 29, Forfeit
1839, 5, Win
1839, 40,

SQL2: ??

1850, 30,
1850, 5,

---

You can do this in two ways, depending on the SQL supported by your DBMS.

Using WHERE ... IN:

SELECT match_id, user_id, result FROM GAME
WHERE match_id IN (
    SELECT DISTINCT match_id FROM GAME WHERE result <> '' GROUP BY match_id)
GROUP BY match_id

Using a derived table:

SELECT g.match_id, g.user_id, g.result
FROM GAME AS g INNER JOIN (
    SELECT DISTINCT match_id FROM GAME WHERE result <> ''  GROUP BY match_id
) AS m ON g.match_id = m.match_id
GROUP BY match_id

The idea is the same in both statements, first get the match_id from those records that have result <> '', notice the use of DISTINCT and GROUP BY, this way you'll get the records for which a match_id has a non-blank result.

Then get the info you need for all records that have a match_id in the first group of records, that is those records with a match_id identified as having a non-blank result.

---

add ORDER BY match_id

to the end of your query.

---

I think something like this will work for your first group:

SELECT match_id,user_id,result
FROM GAME G
WHERE
EXISTS (SELECT 1 FROM GAME WHERE match_id=G.match_id and result <> '')

And for the second group:

SELECT match_id,user_id,result
FROM GAME G
WHERE
NOT EXISTS (SELECT 1 FROM GAME WHERE match_id=G.match_id and result <> '')

But you should probably look into why there are unmatched records like that in the first place.

---

What about

SELECT * FROM ( SELECT * from GAME where result <> '' ) as x ORDER BY x.match_id

?

---

Assuming you are using SQL Server 2005 or newer:

Try this to for your SQL1:

with nonEmptyGames as
(
    SELECT match_id
    FROM Game
    GROUP BY Match_id
    having MAX(Result) != ''
)
SELECT * FROM Games as g
INNER JOIN nonEmptyGames  as neg
ON g.match_id = neg.match_id

and this for SQL2:

with nonEmptyGames as
(
    SELECT match_id
    FROM Game
    GROUP BY Match_id
    having MAX(Result) != ''
)
SELECT * FROM Games as g
LEFT JOIN nonEmptyGames  as neg
ON g.match_id = neg.match_id
WHERE neg.match_id is null