473,226 Members | 1,377 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

RIGHT OUTER JOIN?

In my (admittedly brief) sojurn as an SQL programmer I've often admired
"outer joins" in textbooks but never really understood their use. I've
finally come across a problem that I think is served by an outer join.

-- This table stores the answer to each test question
CREATE TABLE TestResults (
studentId varchar (15) NOT NULL,
testId int NOT NULL REFERENCES Tests(testId),
qId int NOT NULL REFERENCES TestQuestions(qId),
response int NOT NULL REFERENCES TestDistractors(dId),
CONSTRAINT PK_TestResults
PRIMARY KEY NONCLUSTERED (testId, studentId, qId),
)

-- This table defines which questions are on which tests
CREATE TABLE TestQuestions_Tests (
testId int NOT NULL REFERENCES Tests(testId),
qId int NOT NULL REFERENCES TestQuestions(qId),
)

(Table Tests contains housekeeping information about a particular test,
TestQuestions defines individual questions, TestDistractors lists the
possible responses.)

In schematic form, the simplest form of my problem is to find all the
questions that haven't been answered. That would be:

SELECT tqt.qId
FROM TestResults AS tr
RIGHT OUTER JOIN TestQuestions_Tests AS tqt
ON tr.testId = tqt.testId AND tr.qId = tqt.qId
WHERE tr.qId is NULL

So far I think this is pretty straightforward and an efficient solution.
Agreed?

But my real problem is a little bit more complex. What I really want to
know is "for a given student, on a given test, which questions haven't
been answered?"

So now I have:

SELECT tqt.qId
FROM TestResults AS tr
RIGHT OUTER JOIN TestQuestions_Tests AS tqt
ON tr.testId = tqt.testId AND tr.qId = tqt.qId
WHERE tqt.testId = '1' AND tr.studentId = '7' AND tr.qId IS NULL

Is this the canonical form of the solution to my problem? It seems to
me like it is generating a whole slew of rows and then filtering them.
Is there a more elegant or efficient way to do it?

-- Rick

Jul 20 '05 #1
6 6219
Guinness Mann (GM***@dublin.com) writes:
But my real problem is a little bit more complex. What I really want to
know is "for a given student, on a given test, which questions haven't
been answered?"


This is how I would write it:

SELECT tqt.qId
FROM TestQuestions_Tests tqt
WHERE NOT EXISTS (SELECT *
FROM TestResults tr
WHERE tr.studentId = @studentid
AND tr.testId = @testid
AND tqt.testId = tr.testId)
AND tqt.testId = @testid

While outer joins can be used for NOT EXISTS logic, I prefer NOT EXISTS,
as it more clearly states what the query is all about.

Performancewise, outer join can sometimes be better, but it might just
as well be slower.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
In article <MP************************@news.newsguy.com>,
GM***@dublin.com says...
So now I have:

SELECT tqt.qId
FROM TestResults AS tr
RIGHT OUTER JOIN TestQuestions_Tests AS tqt
ON tr.testId = tqt.testId AND tr.qId = tqt.qId
WHERE tqt.testId = '1' AND tr.studentId = '7' AND tr.qId IS NULL

Is this the canonical form of the solution to my problem? It seems to
me like it is generating a whole slew of rows and then filtering them.


(Yes, I'm talking to myself)

Whether it is or is not the canonical form, it's not a solution. <sigh>
If tr.qId IS NULL, then so is tr.studentId.... If I remove the
tr.studentId = '7' clause then I get the unanswered questions for *all*
students.

I just want to get a list of the questions on a particular test and emit
a list of the ones that haven't been answered by a particular student.

Don't tell me I'm going to have to resort to old-fashioned 3-gen
procedural programming to solve this problem. Any ideas?

-- Rick
Jul 20 '05 #3
In article <Xn**********************@127.0.0.1>, so****@algonet.se
says...
This is how I would write it:

SELECT tqt.qId
FROM TestQuestions_Tests tqt
WHERE NOT EXISTS (SELECT *
FROM TestResults tr
WHERE tr.studentId = @studentid
AND tr.testId = @testid
AND tqt.testId = tr.testId)
AND tqt.testId = @testid


Being a translation of mine, that had the same problem mine had -- it
returned no rows. (I know, I know, I didn't supply any create
statements. I have some but I didn't want to clutter up what I thought
was a conceptually simple problem.)

Here's what I made work:

SELECT tqt.qid
FROM
( SELECT testId, qId
FROM testResults
WHERE studentId = @studentId and testid = @testId
)
AS tr
RIGHT OUTER JOIN TestQuestions_Tests AS tqt
ON tr.qId = tqt.qId
WHERE tr.qId IS NULL AND tqt.testId = @testId

By prefiltering the data I avoid retrieving *everyone's* unanswered
questions. How would I convert that one to "NOT EXISTS?"

-- Rick
Jul 20 '05 #4
Guinness Mann wrote:
In my (admittedly brief) sojurn as an SQL programmer I've often admired
"outer joins" in textbooks but never really understood their use. I've
finally come across a problem that I think is served by an outer join.

-- This table stores the answer to each test question
CREATE TABLE TestResults (
studentId varchar (15) NOT NULL,
testId int NOT NULL REFERENCES Tests(testId),
qId int NOT NULL REFERENCES TestQuestions(qId),
response int NOT NULL REFERENCES TestDistractors(dId),
CONSTRAINT PK_TestResults
PRIMARY KEY NONCLUSTERED (testId, studentId, qId),
)

-- This table defines which questions are on which tests
CREATE TABLE TestQuestions_Tests (
testId int NOT NULL REFERENCES Tests(testId),
qId int NOT NULL REFERENCES TestQuestions(qId),
)


Why is TestQuestions not just Questions (or Question -- I find
singular table names usually work out better, if only because I can
always make the primary key <table name>ID). If it was just Questions,
then TestQuestions_Tests would just be TestQuestions. And why
TestDistractors, rather than just Answers?

Anyway, TestQuestions_Tests should have its own key field.
That would simplify TestResults, and eliminate the possibility
that the student is recorded as answering a question that wasn't
on the test.

If you ever delete from any of these tables, you'll want to make
sure all the foreign key fields are indexed. Otherwise, for
instance, any time you delete a row from TestQuestions_Tests,
a table scan of TestResults will be done to make sure the delete
can be allowed.

Bill

Jul 20 '05 #5
Guinness Mann wrote:

Being a translation of mine, that had the same problem mine had -- it
returned no rows. (I know, I know, I didn't supply any create
statements. I have some but I didn't want to clutter up what I thought
was a conceptually simple problem.)

Here's what I made work:

SELECT tqt.qid
FROM
( SELECT testId, qId
FROM testResults
WHERE studentId = @studentId and testid = @testId
)
AS tr
RIGHT OUTER JOIN TestQuestions_Tests AS tqt
ON tr.qId = tqt.qId
WHERE tr.qId IS NULL AND tqt.testId = @testId

By prefiltering the data I avoid retrieving *everyone's* unanswered
questions. How would I convert that one to "NOT EXISTS?"


SELECT TestQuestions_Tests.qid
FROM TestQuestions_Tests
WHERE
TestQuestions_Tests = @TestId
AND NOT EXISTS
(
SELECT *
FROM testResults
WHERE
studentId = @studentId
AND testid = @testId
AND TestQuestions_Tests.qid = TestResults.qID
)
BTW, I'm not sure I've ever seen a right join in production code,
only left. Conceptually, it's the same thing, but I always think
of queries left-to-right, so it would throw me off when reading it.

Bill

Jul 20 '05 #6
In article <3F**************@Ameritech.Net>, WC********@Ameritech.Net
says...
-- This table stores the answer to each test question
CREATE TABLE TestResults (
studentId varchar (15) NOT NULL,
testId int NOT NULL REFERENCES Tests(testId),
qId int NOT NULL REFERENCES TestQuestions(qId),
response int NOT NULL REFERENCES TestDistractors(dId),
CONSTRAINT PK_TestResults
PRIMARY KEY NONCLUSTERED (testId, studentId, qId),
)

-- This table defines which questions are on which tests
CREATE TABLE TestQuestions_Tests (
testId int NOT NULL REFERENCES Tests(testId),
qId int NOT NULL REFERENCES TestQuestions(qId),
)

Why is TestQuestions not just Questions (or Question -- I find
singular table names usually work out better, if only because I can
always make the primary key <table name>ID). If it was just Questions,
then TestQuestions_Tests would just be TestQuestions. And why
TestDistractors, rather than just Answers?


I didn't even mention TestQuestions_Distractors, TextResources,
TestQuestions_AudioResources, TestQuestions_TextResources or
AudioResources. :-)

Technically (hey, I'm not a content-provider, I'm just the implementer)
the "answer" is the distractor that represents the correct response.
Therefore we have a table of distractors and elsewhere the distractorId
of the correct response is noted.
If you ever delete from any of these tables, you'll want to make
sure all the foreign key fields are indexed. Otherwise, for
instance, any time you delete a row from TestQuestions_Tests,
a table scan of TestResults will be done to make sure the delete
can be allowed.


Actually, I go the other way on that. I delete from the Tests or
TestQuestions table, and it cascades into the intersection tables.
Thanks for the help with the "NOT EXISTS" part.

-- Rick

Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly...
4
by: Omavlana | last post by:
Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from...
1
by: Omavlana | last post by:
Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from...
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
3
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...
0
by: K k | last post by:
Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from...
1
by: Andreas Bauer | last post by:
Hi, when I try a left outer join on one table everything works fine: select * from (tourist.users u left outer join tourist.user_extended_info ue on (u.id = ue.id)) But now I need to do...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.