Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)

I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a score for each test.
There are a different numbers of trials for each pair of users.

In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int), testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)

Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA & userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.

Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)

There are always 2 users for each trial id.

I want a query which will return all scores for all users for all trials, BUT must include NULLs if a user did not take a test on that trial.

I thought it may involve a cross join between the Tests table and the Trials table.

Any help greatly appreciated.this cannot be correct --

Scores - scoreid(primary,int), userid(int), trialid(int), userid(int), testid(int), score(int)

you cannot have two columns in the same table with the same name

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.
If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.
|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialid

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialidsql

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA
&
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4
)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Tria
ls
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialid

Outer Join - Returning Customers Who Haven't ordered a product

Hi,
Is there any way to make the following LEFT OUTER JOIN statement work in a
similar fashion as the SUBQUERY statement that follows it? Customers 1 and
3
haven't ordered anything for the OrderDate '2006-01-01' and I want to exclud
e
them from the display in the LEFT OUTER JOIN query (like they are excluded i
n
the SUBQUERY statement).
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
customerid customername
-- ----
1 Brian
2 Andy
3 Steve
4 Delaney
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select c.customerid from orders o
where c.customerid = o.customerid
and o.orderdate = '2006-01-01' )
customerid customername
-- ----
2 Andy
4 Delaney
Thanks,
IanHello, Ian
You can use this query:
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
WHERE o.customerid IS NULL
But I think it's better with a subquery (and it's more clear):
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select o.customerid from orders o
where o.orderdate = '2006-01-01' )
Razvan|||Razvan

> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
In this case we may want to add AND o.customerid IS NOT NULL
Just my 2 cents
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139292357.248363.83490@.g14g2000cwa.googlegroups.com...
> Hello, Ian
> You can use this query:
> select c.customerid
> , c.customername
> from customers c
> left outer join orders o
> on c.customerid = o.customerid
> and o.orderdate = '2006-01-01'
> WHERE o.customerid IS NULL
> But I think it's better with a subquery (and it's more clear):
> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
> Razvan
>|||Hi, Uri
You are right. Since the OP did show the DDL, I assumed that a
"customerid" column in an "orders" table would not allow NULL-s, but I
may be wrong... :) But in this case, I still think that he should fix
the DDL, not the query. Anyway, if we are not talking about orders and
customers, your comment raises a very good point.
Razvan|||Hi Razvan,
Thanks for the very quick answer. I should have tried that myself. I
actually simplified the query because what I really need to do is this: "Lis
t
all customers who have never ordered a particular product". Again, I can do
this via a Subquery with no problem but I'm told this can also be done via
LEFT OUTER JOINS and I can't get the following to work, so I must be missing
something.
SELECT c.CustomerID, p.productname
FROM Customers c
LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
AND p.productname = 'Toshiba TV'
GROUP BY c.CustomerID,p.productname
ORDER BY c.CustomerID
The result set I get is this:
CustomerID productname
-- ----
1001 Toshiba TV
1001 NULL
1002 NULL
1003 Toshiba TV
1003 NULL
1004 Toshiba TV
1004 NULL
1005 NULL
1005 Toshiba TV
1006 NULL
1007 NULL
1007 Toshiba TV
1008 Toshiba TV
1008 NULL
1009 Toshiba TV
1009 NULL
1010 NULL
1010 Toshiba TV
What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 from
the display because they have never ordered a "Toshiba TV". Adding a WHERE
clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY claus
e
doesn't work (it results in 0 rows being returned).
Thanks,
Ian|||Hi guys,
Actually, adding an AND clause won't work here as it returns all four rows
that I mention in my original post. My original SUBQUERY without the AND
clause gives me what I want (i.e. just the two rows). Nulls are not allowed
for Customerid in the Order table.

> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01'
and o.customerid IS NULL)
"Razvan Socol" wrote:

> Hi, Uri
> You are right. Since the OP did show the DDL, I assumed that a
> "customerid" column in an "orders" table would not allow NULL-s, but I
> may be wrong... :) But in this case, I still think that he should fix
> the DDL, not the query. Anyway, if we are not talking about orders and
> customers, your comment raises a very good point.
> Razvan
>|||Rats...I got it backwards. What I meant to say at the bottom of my post was
that I *only* want to report Customers 1002 and 1006 because they are the
only ones who have *not* ordered a Toshiba TV at some point in time.
"Ian Fisher" wrote:

> Hi Razvan,
> Thanks for the very quick answer. I should have tried that myself. I
> actually simplified the query because what I really need to do is this: "L
ist
> all customers who have never ordered a particular product". Again, I can
do
> this via a Subquery with no problem but I'm told this can also be done via
> LEFT OUTER JOINS and I can't get the following to work, so I must be missi
ng
> something.
> SELECT c.CustomerID, p.productname
> FROM Customers c
> LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
> LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
> LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
> AND p.productname = 'Toshiba TV'
> GROUP BY c.CustomerID,p.productname
> ORDER BY c.CustomerID
> The result set I get is this:
> CustomerID productname
> -- ----
> 1001 Toshiba TV
> 1001 NULL
> 1002 NULL
> 1003 Toshiba TV
> 1003 NULL
> 1004 Toshiba TV
> 1004 NULL
> 1005 NULL
> 1005 Toshiba TV
> 1006 NULL
> 1007 NULL
> 1007 Toshiba TV
> 1008 Toshiba TV
> 1008 NULL
> 1009 Toshiba TV
> 1009 NULL
> 1010 NULL
> 1010 Toshiba TV
> What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 fro
m
> the display because they have never ordered a "Toshiba TV". Adding a WHER
E
> clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY cla
use
> doesn't work (it results in 0 rows being returned).
> Thanks,
> Ian|||Hello, Ian
Please post DDL (as CREATE TABLE statements) and sample data (as INSERT
INTO ... VALUES ...). See: http://www.aspfaq.com/etiquette.asp?id=5006
I think it's possible to do this with LEFT JOIN-s instead of a "NOT IN"
subquery, but it will require a derived table (a subquery in the FROM
clause) or a view.
Razvan|||Hi Razvan,
I figured it out with the help of a colleague....thanks a lot for your help
.
SELECT c.CustomerID, c.CustFirstName, c.CustLastName
FROM Customers c
LEFT JOIN
(SELECT c1.customerid as cid from customers c1
join orders o on o.CustomerID = c1.customerid
JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
JOIN Products p ON od.ProductNumber = p.ProductNumber
WHERE p.productname = 'Viscount Microshell Helmet') c2
ON c2.cid = c.CustomerID
WHERE c2.cid IS NULL
Best Regards,
Ian|||> [...] what I really need to do is this: "List all customers
> who have never ordered a particular product".
> Again, I can do this via a Subquery with no problem
> but I'm told this can also be done via LEFT OUTER JOINS [...]
Since you did not provide DDL and sample data, I have used Northwind
for the following queries. After a little more thinking, I have found a
way to write this query using outer joins, even without using a derived
table.
So, instead of this simple and clear query:
SELECT CompanyName FROM Customers
WHERE CustomerID NOT IN (
SELECT CustomerID FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
WHERE p.ProductName='Chang'
)
we can write a query using LEFT JOIN-s, with a weird placement of the
ON clauses:
SELECT CompanyName FROM Customers c
LEFT JOIN Orders o
LEFT JOIN [Order details] od
LEFT JOIN Products p ON od.ProductID=p.ProductID
ON o.OrderID=od.OrderID
ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
or we can use a RIGHT JOIN:
SELECT CompanyName FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
RIGHT JOIN Customers c ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
However, if we examine the execution plans, we can see that the first
query (which is also easier to understand) has a lower cost than the
other two queries (which have the same execution plan).
For more informations, see:
http://groups.google.com/group/micr...br />
2579509b
http://groups.google.com/group/micr...br />
22a96b90
Razvan

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.