Query for show each value from another table

Question

I dont know my titled question is correct, but let me explain what i am looking for.

I have two tables.

User Score

user (primary key)
game (varchar)
score (int)

Game

game (varchar)

---

Client User Score data looks like this

user    game             score
Chris   naruto           10
Gale    dungen           9
Donna   winning eleven   8

Game table data looks like this

game
dungen
naruto
winning eleven

---

Is it possible to create a view that will look like this :

user      game                score
Chris     naruto              10
          dungen              0 
          winning eleven      0
Gale      naruto              0
          dungen              9
          winning eleven      0
Donna     naruto              0
          dungen              0
          winning eleven      8

Thanks in advance.

Answer 1

You need first get a distinct set of all possible combinations of user+game, using a cross join:

select distinct u.user, g.game
from game g
cross join user u
;

This will give below result:

|  USER |           game |
|-------|----------------|
| Chris |         dungen |
| Chris |         naruto |
| Chris | winning eleven |
|  Gale |         dungen |
|  Gale |         naruto |
|  Gale | winning eleven |
| Donna |         dungen |
| Donna |         naruto |
| Donna | winning eleven |

Then you can join this resultset (using outer join) to the user table in this way:

SELECT * 
FROM (
    select distinct u.user, g.game
    from game g
    cross join user u
) x
LEFT JOIN user u ON x.user = u.user and x.game = u.game
;

The above query gives you the following resultset:

|  user |           game |   USER |           game |  score |
|-------|----------------|--------|----------------|--------|
| Chris |         naruto |  Chris |         naruto |     10 |
|  Gale |         dungen |   Gale |         dungen |      9 |
| Donna | winning eleven |  Donna | winning eleven |      8 |
| Chris |         dungen | (null) |         (null) | (null) |
| Chris | winning eleven | (null) |         (null) | (null) |
|  Gale |         naruto | (null) |         (null) | (null) |
|  Gale | winning eleven | (null) |         (null) | (null) |
| Donna |         dungen | (null) |         (null) | (null) |
| Donna |         naruto | (null) |         (null) | (null) |

Next you can use the above resultset to generate a report using simple SUM and GROUB BY.
But first you need to replace all nulls with 0, because a sum of nulls gives null value.
In this way:

SELECT x.user, x.game, 
       sum( case when score is not null then score else 0 end ) as score
FROM (
    select distinct u.user, g.game
    from game g
    cross join user u
) x
LEFT JOIN user u ON x.user = u.user and x.game = u.game
GROUP BY x.user, x.game
order by x.user, x.game
;

This query produces the following:

|  user |           game | score |
|-------|----------------|-------|
| Chris |         dungen |     0 |
| Chris |         naruto |    10 |
| Chris | winning eleven |     0 |
| Donna |         dungen |     0 |
| Donna |         naruto |     0 |
| Donna | winning eleven |     8 |
|  Gale |         dungen |     9 |
|  Gale |         naruto |     0 |
|  Gale | winning eleven |     0 |

You can find a full runnable example here: http://sqlfiddle.com/#!9/657cdf/12

I am using MySql dialect of SQL in these examples, because it is available on SqlFiddle
but I belive that converting this to MS-Access shouldn't be a problem.

=== EDIT - how to convert this query into MS-Access dialect ===

I've tested this query on MS-Access 2010
There are only two things that are not supported by MS-Access:

---

MS Access doesn't support CROSS JOIN keyword.
It can be converted in this way: How to use cross join in access?
using "old" syntax with comma betwen tables:

SELECT DISTINCT u.user, g.game
FROM game g, user u

---

MS-Access doesn't support Case expressions
: CASE WHEN condition THEN value1 ELSE value2 END
But you can use IIF function instead:
IIF( condition, value1, value2 )
more on this here: Does MS Access support "CASE WHEN" clause if connect with ODBC?

---

The whole query converted to MS-Access dialect:

SELECT x.user, x.game, 
       sum( IIF( u.score IS NOT NULL, u.score, 0)) as score
FROM (
    SELECT DISTINCT u.user, g.game
    FROM game g, user u
) x
LEFT JOIN user u ON x.user = u.user and x.game = u.game
GROUP BY x.user, x.game
ORDER BY x.user, x.game

And a result of the query (in MS-Access):