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.

osql and 'real' csv format

I want to use osql to generate the query report in comma separated format
(csv), but even though I specified output file as report.csv, the data in the
file is not comma separated, instead, it's just like what we see when we do
query with SQL QUERY ANALYZER with "results in text" menu seletect. for
example, suppose the query is select column1, column2 from table1, and I
expect the data in .csv file as
r11,r12
r21,r22
r31,r32
instead, I get:
header1 header2
-- --
r11 r12
r21 r22
r31 r32
my statement is:
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
c:\mydb\report.csv
what am i missing here and how to get report in 'real' csv format?
Appreciate your help and time on this, THANKS!
You may try something like
[script]
bcp "SELECT au_fname, au_lname FROM pubs..Authors ORDER BY au_lname"
queryout c:\Authors.txt -t , -r \n -c -SYourServerName -T
[/script]
And take a look at this article "Showdown-bcp vs. DTS"
http://www.windowsitpro.com/Articles...rticleID=19760
Cristian Lefter, SQL Server MVP
"gongzuo" <gongzuo@.discussions.microsoft.com> wrote in message
news:A7B5CE27-E2C1-4BCE-9B24-7EBF0588C921@.microsoft.com...
>I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in
> the
> file is not comma separated, instead, it's just like what we see when we
> do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||Try this
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
c:\mydb\report.csv
"gongzuo" wrote:

> I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in the
> file is not comma separated, instead, it's just like what we see when we do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||Try this
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
c:\mydb\report.csv
"gongzuo" wrote:

> I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in the
> file is not comma separated, instead, it's just like what we see when we do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||it works, Thanks!
one more question:
how to generate a 'real' .xls format report using the same method?
or what's the col_separator character for an .xls format?
[i changed the generated .csv to .xls and the data are not align with
the columns in .xls file; I changed the '.csv' to '.xls' in the statement
only to generated the same in wrong format.
"msdnbuddy" wrote:
[vbcol=seagreen]
> Try this
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
> c:\mydb\report.csv
> "gongzuo" wrote:
|||it works, thanks!
one more: what's the colomn-separator character for an .xls format?
i changed the report generated by the above statement from 'report.csv'
to 'report.xls' and the data are not align with the columns in the file;
I changed the '.csv' to '.xls' in the statement and only get the similar
wrong .xls format]
"msdnbuddy" wrote:
[vbcol=seagreen]
> Try this
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
> c:\mydb\report.csv
> "gongzuo" wrote:
|||The problem that was causing the columns to not align was that the column
width of the output file was (by default) 80 cols. This can be corrected
with the -w switch
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -w
500 -o
c:\mydb\report.csv
Depending on how many columns are returned in your select statement, I would
suggest that you increase or decrease the width. I did a "select * from
orders" on the northwind db and widht of 500 resulted in a well formatted
..xls file. Give it a try.
Cheers.
"gongzuo" wrote:
[vbcol=seagreen]
> it works, thanks!
> one more: what's the colomn-separator character for an .xls format?
> i changed the report generated by the above statement from 'report.csv'
> to 'report.xls' and the data are not align with the columns in the file;
> I changed the '.csv' to '.xls' in the statement and only get the similar
> wrong .xls format]
> "msdnbuddy" wrote:
|||Thanks !
another problem :
using -s","
I couldn't get the whole date_time , for example, for the date_time is
2005-04-20 14:38:36.000, I only get 38:36:0
I did "select BirthDate from employees" on the nothwind db and get 00:00:0
for all the 9 records.
appreciate your help and time!
"msdnbuddy" wrote:
[vbcol=seagreen]
> The problem that was causing the columns to not align was that the column
> width of the output file was (by default) 80 cols. This can be corrected
> with the -w switch
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -w
> 500 -o
> c:\mydb\report.csv
> Depending on how many columns are returned in your select statement, I would
> suggest that you increase or decrease the width. I did a "select * from
> orders" on the northwind db and widht of 500 resulted in a well formatted
> .xls file. Give it a try.
> Cheers.
> "gongzuo" wrote:
|||I too am having this date problem. Also, can osql use tabs as delimiters? -s
"\t" doesn't work.
"gongzuo" wrote:
[vbcol=seagreen]
> Thanks !
> another problem :
> using -s","
> I couldn't get the whole date_time , for example, for the date_time is
> 2005-04-20 14:38:36.000, I only get 38:36:0
> I did "select BirthDate from employees" on the nothwind db and get 00:00:0
> for all the 9 records.
> appreciate your help and time!
> "msdnbuddy" wrote: