Hope this is as easy as I think, but I am struggling to find answer in BOL,
etc.
I have 3 simple tables and want to link them on the same field, "ProductID".
The first table has all productid's on open SalesOrders and the qty sold.
The second table has productid's in Inventory for OnHand quanitity,
and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
I need to make sure all of the records in table one (SalesOrders) are
included regardless of the data in the other two tables. Additionally I wan
t
to return ONLY the ProductId's from the first table. LeftOuter Join isn't
working for me.
Obviously I am a newbie, and it's probably a dumb question, but you guys
always have the right answer really fast...Chuck,
I think that is equivalent to:
select ProductID from SalesOrders
AMB
"Chuck" wrote:
> Hope this is as easy as I think, but I am struggling to find answer in BOL
,
> etc.
> I have 3 simple tables and want to link them on the same field, "ProductID
".
> The first table has all productid's on open SalesOrders and the qty sold.
> The second table has productid's in Inventory for OnHand quanitity,
> and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
> I need to make sure all of the records in table one (SalesOrders) are
> included regardless of the data in the other two tables. Additionally I w
ant
> to return ONLY the ProductId's from the first table. LeftOuter Join isn't
> working for me.
> Obviously I am a newbie, and it's probably a dumb question, but you guys
> always have the right answer really fast...|||Sorry I was vague. I would like to return...
ProductID.SalesOrders
QtyOrdered.SalesOrders
QtyOnHand.Inventory
QtyOnPO.PurchaseOrders
returning all of the ProductID's and QtyOrdered in SalesOrders
and the quantities in Inventory and PurchaseOrders regardless of whether
that particular ProductID exists in that table (returning a null value if it
doesn't exist I presume).
In other words, two left outer joins to SalesOrders.
"Alejandro Mesa" wrote:
> Chuck,
> I think that is equivalent to:
> select ProductID from SalesOrders
>
> AMB
> "Chuck" wrote:
>|||Try,
select
so.ProductID,
so.QtyOrdered,
i.QtyOnHand,
po.QtyOnPO
from
SalesOrders as so
left join
Inventory as i
on so.ProductID = i.ProductID
left join
PurchaseOrders as po
on so.ProductID = po.ProductID
AMB
"Chuck" wrote:
> Sorry I was vague. I would like to return...
> ProductID.SalesOrders
> QtyOrdered.SalesOrders
> QtyOnHand.Inventory
> QtyOnPO.PurchaseOrders
> returning all of the ProductID's and QtyOrdered in SalesOrders
> and the quantities in Inventory and PurchaseOrders regardless of whether
> that particular ProductID exists in that table (returning a null value if
it
> doesn't exist I presume).
> In other words, two left outer joins to SalesOrders.
> "Alejandro Mesa" wrote:
>|||Thanks, That worked perfectly. For some reason I was getting an error when
attempting to create two outer joins in one query. Wonder why?
"Alejandro Mesa" wrote:
> Try,
> select
> so.ProductID,
> so.QtyOrdered,
> i.QtyOnHand,
> po.QtyOnPO
> from
> SalesOrders as so
> left join
> Inventory as i
> on so.ProductID = i.ProductID
> left join
> PurchaseOrders as po
> on so.ProductID = po.ProductID
>
> AMB
> "Chuck" wrote:
>sql
Showing posts with label struggling. Show all posts
Showing posts with label struggling. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
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
(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
(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
(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
(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
Friday, March 23, 2012
Outer Join Challenge
I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>
Outer Join Challenge
I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>
Outer Join Challenge
I'm struggling with the creation of a SQL 2005 statement involving two
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>sql
tables. I've simplified the situation below.
Table: Opportunity
Columns:
Name - always has a value
OpportunityID - key field link
Status - 0 or 1 for Open or Closed
Table: Activities
Owner - always has a value
RegardingObjectID - key field link
Status - 0 or 1 for Open or Closed
I am attempting to isolate those opportunities that do not have an open
activity. I thought an outer join would do the trick, and I would just look
for those opportunities with a null Owner. Below is my statement:
SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON Opportunity.OpportunityID = Activities.RegardingObjectID
where Activities.Status = 0
It seems as if the join is evaluated first before the filters, so that
opportunities that have no open activities are being dropped because the
link is pulling in the closed activities. In other words, the only results
that are returned with a null Owner are those that have neither an open nor
a close activity.
This is a SQL statement that I am going to use for a SQL Report using SQL
Reporting Services, so I think I need to accomplish this with one SQL
statement.
Is my only avenue to create a restricted view of the Activities table and
use that in my report?SELECT Opportunity.Name, Activities.Owner
FROM Opportunity LEFT OUTER JOIN Activities
ON (Opportunity.OpportunityID = Activities.RegardingObjectID and
Activities.Status = 0)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Cindy" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:%23nrS$uK8GHA.2128@.TK2MSFTNGP05.phx.gbl...
> I'm struggling with the creation of a SQL 2005 statement involving two
> tables. I've simplified the situation below.
> Table: Opportunity
> Columns:
> Name - always has a value
> OpportunityID - key field link
> Status - 0 or 1 for Open or Closed
> Table: Activities
> Owner - always has a value
> RegardingObjectID - key field link
> Status - 0 or 1 for Open or Closed
> I am attempting to isolate those opportunities that do not have an open
> activity. I thought an outer join would do the trick, and I would just
> look for those opportunities with a null Owner. Below is my statement:
> SELECT Opportunity.Name, Activities.Owner
> FROM Opportunity LEFT OUTER JOIN Activities
> ON Opportunity.OpportunityID = Activities.RegardingObjectID
> where Activities.Status = 0
> It seems as if the join is evaluated first before the filters, so that
> opportunities that have no open activities are being dropped because the
> link is pulling in the closed activities. In other words, the only
> results that are returned with a null Owner are those that have neither an
> open nor a close activity.
> This is a SQL statement that I am going to use for a SQL Report using SQL
> Reporting Services, so I think I need to accomplish this with one SQL
> statement.
> Is my only avenue to create a restricted view of the Activities table and
> use that in my report?
>sql
Subscribe to:
Posts (Atom)