help with outer join

[HX]

I thought I knew how to do this, but for some reason it's not working - I
could use some help.

I have two tables:

MSTR_TEST - a master list of available tests, key is testID (testID,
testname)
USER_TEST - one record per user, per test, IF test has been taken. Key is
userID. (userID, testID, score)

I want to create a query that will show me a SET of testIDs per user that
exists in the User_Test table. If there is a record for any particular
testID, I want to show the details for that record, but if there is no
record for that user, for that particular test, I want to show a record, but
NULLS on the user_test side.

I thought this was a left outer join, but I can't get it to work.

Sample table data would be:

Mstr_Test:

testID testname
---------------------------
1 Test1
2 Test2
3 Test3


User_Test:

userID testID score
-------------------------------
1 1 95
1 2 98
2 1 96


The results I want in the query are:

userID testID score testname
-----------------------------------------
1 1 95 test1
1 2 98 test2
1 3 [null] test3
2 1 96 test1
2 2 [null] test2
2 3 [null] test3

Is this doable?!

Ultimately I will filter by userID, if that's of any help at all -- this is
the back end to an .asp page, so I will do a call to pull the records for
one particular userID.

Thanks for any help.

 

[Ken Snell \(MVP\)]

SELECT MSTR_TEST.testID, USER_TEST.userID
FROM MSTR_TEST LEFT JOIN USER_TEST
ON MSTR_TEST.testID = USER_TEST.testID;

 

[Amy Blankenship]

I thought I knew how to do this, but for some reason it's not working - I
could use some help.

I have two tables:

MSTR_TEST - a master list of available tests, key is testID (testID,
testname)
USER_TEST - one record per user, per test, IF test has been taken. Key is
userID. (userID, testID, score)

I want to create a query that will show me a SET of testIDs per user that
exists in the User_Test table. If there is a record for any particular
testID, I want to show the details for that record, but if there is no
record for that user, for that particular test, I want to show a record,
but NULLS on the user_test side.

Since you're doing this from a web page, you have two options. First, you
can do this with TWO Joins, or second, you can do this with a SHAPE query.

Option 1:

SELECT User.UserName, MSTR_Test.testname, User_Test.Score FROM User LEFT
JOIN
(User_Test RIGHT JOIN Mstr_Test ON User_Test.TestID = Mstr_Test.TestID)
ON User.UserID = User_Test.UserID

Option 2 (requires you to use MS Datashape connection):

SHAPE {SELECT User.UserName FROM Users}
APPEND ({SELECT Mstr_Test.testname, User_Test.Score FROM Mstr_Test LEFT
JOIN
User_Test ON Mstr_Test.TestID = User_Test.TestID} AS UserTestScores
RELATE UserID to UserID)

HTH;

Amy

 

[HX]

Thanks for replying, Ken.

That's what I was doing - I was just forgetting to filter in my specific
user list using OR user.test.testid is null !! Seeing you affirm my query
forced me to see what I was overlooking. Thanks again.

SELECT MSTR_TEST.testID, USER_TEST.userID
FROM MSTR_TEST LEFT JOIN USER_TEST
ON MSTR_TEST.testID = USER_TEST.testID;

--

Ken Snell
<MS ACCESS MVP>

I thought I knew how to do this, but for some reason it's not working - I
could use some help.

I have two tables:

MSTR_TEST - a master list of available tests, key is testID (testID,
testname)
USER_TEST - one record per user, per test, IF test has been taken. Key
is userID. (userID, testID, score)

I want to create a query that will show me a SET of testIDs per user that
exists in the User_Test table. If there is a record for any particular
testID, I want to show the details for that record, but if there is no
record for that user, for that particular test, I want to show a record,
but NULLS on the user_test side.

I thought this was a left outer join, but I can't get it to work.

Sample table data would be:

Mstr_Test:

testID testname
---------------------------
1 Test1
2 Test2
3 Test3


User_Test:

userID testID score
-------------------------------
1 1 95
1 2 98
2 1 96


The results I want in the query are:

userID testID score testname
-----------------------------------------
1 1 95 test1
1 2 98 test2
1 3 [null] test3
2 1 96 test1
2 2 [null] test2
2 3 [null] test3

Is this doable?!

Ultimately I will filter by userID, if that's of any help at all -- this
is the back end to an .asp page, so I will do a call to pull the records
for one particular userID.

Thanks for any help.