Q:

sql join -vs- where clause not producing the same result?

I am a bit boggled as to why these two SQL constructs do not yield the same result.
SQL#1 return 2 identical records (dups) when only one exists in the defects table... see next sql
SELECT  * 
FROM Defects d
    JOIN StatusCode C ON C.CodeName = d.Status AND c.scid = 10
WHERE d.AssignedTo='me' 

SQL#2 reruns 1 record - this is correct cause lookign at raw data there is one defect not closed for 'me' 
SELECT  * 
FROM Defects d
WHERE d.AssignedTo='me' AND Status <> 'closed'

all i am doing is instead of using a negative where status not in something , using a positive by way of the join to records that have every value defect status other than closed
why does this happen, and how can i alter my select with the join to corect its result. i tried using DISTINCT but it fails with: 

The ntext data type cannot be selected
  as DISTINCT because it is not
  comparable.

there are no status codes that are 'closed', not a single one:
select * from StatusCode where scid = 10

results in these values:
Fixed
New
Ready for Retest
Failed Retest
Quality Follow Up
Reopen
Rejected
Consumer
In Coding
Open
Fixed
New
Ready for Retest
Failed Retest
Quality Follow Up
Reopen
Rejected
Consumer
In Coding
Open

A:

The inner join will return all matching combinations of rows, so there must be two rows in the StatusCode table that match the "Status" value of your Defect (and have scid = 10).
FixedNewReady for RetestFailed RetestQuality Follow UpReopenRejected ConsumerIn CodingOpenFixedNewReady for RetestFailed RetestQuality Follow UpReopenRejected ConsumerIn CodingOpen
Not sure if I parsed your list exactly right, but there do appear to be duplicates. The answer, then, is to either eliminate the duplicates in the StatusCode table, or apply an additional filter to distinguish between them if the duplicates are valid.

