Outer Join not working

"Steve Kass" <skass@.drew.edu> wrote in message
news:evEOZswGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Only two rows of the OUTER JOIN satisfy the WHERE clause of
> the query, (where JobID = 3720). If you want all the rows of the
> outer join, including those with JobID values other than 3720,
> don't include a WHERE clause to restrict what you get to rows with
> JobID = 3720. You may want that condition in the ON clause
> of the outer join, but since you only tell us what you don't want,
> not what you do want, I won't speculate.
I thought I did tell you what I wanted.

> In short, a SELECT query's WHERE clause is always enforced.
> The ON clause of an OUTER JOIN is the one that may not be
> true for all the result set rows.
That was what I was looking for. I always have trouble with Outer Joins.
What worked was:
select S.PositionID,s.QuestionUnique,Question,QuestionType,
Weight,AnswerTime,jobID
from SkillsTest as S
left outer join ApplicantTest j
on (j.questionUnique = s.questionUnique and JobID = 3720)
where PositionID = 25
If I leave out the where clause, it almost works, but I also get all the
rows with any PositionID, so I needed to add that.
I am still not sure why I had to move the JobID test into the "on" clause,
but it worked. I will have to study it more to understand it.
Thanks,
Tom
> SK
>
> tshad wrote:
>
,
,
53
12
from
all
(as
would
s.questionUnique)Might interest you:
http://support.microsoft.com/defaul...kb;en-us;176480
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"tshad" <tfs@.dslextreme.com> wrote in message
news:ur$XX5wGFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:evEOZswGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I thought I did tell you what I wanted.
>
> That was what I was looking for. I always have trouble with Outer Joins.
> What worked was:
> select S.PositionID,s.QuestionUnique,Question,QuestionType,
> Weight,AnswerTime,jobID
> from SkillsTest as S
> left outer join ApplicantTest j
> on (j.questionUnique = s.questionUnique and JobID = 3720)
> where PositionID = 25
> If I leave out the where clause, it almost works, but I also get all the
> rows with any PositionID, so I needed to add that.
> I am still not sure why I had to move the JobID test into the "on" clause,
> but it worked. I will have to study it more to understand it.
> Thanks,
> Tom
>
first.
NULL
> ,
NULL
> ,
2
7
> 53
8
1
1
8
0
0
> 12
2
Answer
> from
want
> all
> (as
> would
> s.questionUnique)
>|||On Thu, 24 Feb 2005 23:49:52 -0800, tshad wrote:
(snip)
>I am still not sure why I had to move the JobID test into the "on" clause,
>but it worked. I will have to study it more to understand it.
Hi Tom,
That's because of the order in which a SELECT statement is evaluated.
The order of evaluation whould, in theory (most DBMS's will reshuffle to
optimize, as long as it doesn't change the end result), be as follows:
1. Evaluate FROM clause. Make a temporary result set that holds all
columns from all tables in the FROM clause, with all the rows that
satisfy the ON conditions.
Rows that don't match the ON condition are not included if it's an INNER
JOIN; for OUTER JOINS, the row from the outer table will be retained
with NULL values substituted for the columns from the inner table.
2. Evaluate WHERE clause. Check each row in the temporary result set
from the previous step against the WHERE condition. The row is retained
only if the WHERE condition evaluates to TRUE. If it evaluates to FALSE
or UNKNOWN, the row is removed from the result set.
3. After this, the GROUP BY, HAVING, SELECT and ORDER BY are processed
(in that order) - but these are not relevant for your question.
In your original question, you had a WHERE clause
where JobID = 3720
where the JobID column is from the inner table of an outer join. For all
rows retained because of the outer join, JobID is NULL. By definition,
the expression NULL = 3270 evaluates to FALSE. Therefor, all rows
retained by the outer join are subsequently removed by the where clause,
effectively changing your query to an inner join.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)