OUTER JOIN

Oi! What follows is a hypothetical situation, but it is a totally analogous to a real problem Im having, but provides an easier model to understand.

Imagine that you have database-driven battleship game and its time to render the board. Also imagine that you have to render more than one board and that the ships are all the size of one point on the grid.

One sql result you need is a list of all the points on the grid, regardless of whether or not there is a ship on it. This will make rendering much easier for you, because you can simply look at the record index to determine if a ship is present. The data that is stored about the position of the ships consists of one record containing the grid index and ship name.

One possible way to retrieve this data is to build a table that you will not change which contains a record for each point on the grid. Is it possible to union or join on this table to retrieve a list of results that contain both unoccupied locations and occupied ones?

Here is what I've come up with, but it contains results that have a null location when there are no ship records:

SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
FULL OUTER JOIN Ships
ON Ships.GridID = Grids.GridID
WHERE
Grids.PlayerID = 1
UNION-- (**not** UNION ALL)
SELECT
Grids.GridID,
GridLocations.GridLocation,
(SELECT ShipName FROM Ships WHERE GridID = Grids.GridID AND GridLocation = GridLocations.GridLocation)
FROM
GridLocations, Grids
WHERE
Grids.PlayerID = 1

In the first sql statement, why not just do a left join? That will select everything from the grid, and then the ship info if it is there.|||Something about Ships to Grids and GridLocations to Ships is preventing this from happening as you would expect:

SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
FULL OUTER JOIN Ships
ON Ships.GridID = Grids.GridID
LEFT JOIN GridLocations
ON GridLocations.GridLocation = Ships.GridLocation
WHERE
Grids.PlayerID = 1

this would return a list of ships if there are any, and if not, a list of grids!
(In this silly scenario, there can be multiple grids with PlayerID = 1|||I meant something like this:

SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
LEFT JOIN Ships
ON Grids.GridID = Ships.GridID
WHERE
Grids.PlayerID = 1

It's hard to tell since I don't have any data to work with to simulate. :)|||Hmm

This would result in only a list of grids, or a list of ships and grids. Like if there were 4 ships scattered in 2 grids:

GridID GridLocation Name
-----------
1 1 MyShip1
1 24 MyShip2
2 18 MyShip3
2 20 MyShip4

However, what I want is a list of all grid locations on all grids:

GridID GridLocation Name
-----------
1 1 MyShip1
1 2 NULL
1 3 NULL
1 4 NULL
1 5 NULL
1 6 NULL
1 7 NULL
...
2 1 NULL
2 2 NULL
2 3 NULL
2 4 NULL
2 5 NULL
2 6 NULL
2 7 NULL|||You need to post some DDL with some sample data, otherwise my guessing won't help.