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开发者_如何学JAVA 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

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).

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

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.

How many rows are returned by this?

SELECT * FROM StatusCode C WHERE c.scid = 10

You may therefore want to do this:

SELECT  * 
FROM Defects d
WHERE d.AssignedTo='me' AND d.Status IN (
    SELECT C.CodeName FROM StatusCode C WHERE C.scid = 10
)

Edit to address your edit: since you have multiple states with scid=10, each of those will be joined to your rows, which is why you get the duplicates. My code suggestion is still valid though.

I would think the problem is here:

JOIN StatusCode C ON C.CodeName = d.Status AND c.scid = 10

The c.scid = 10 should be in the where clause.