Problem with AND NOT in MySql

I am SQL novice and I am working on a PHP+MySql application. The database the application is using has many tables, such as swverforftver:

SWVerID  FileTypeVerID
1111     897897
1111     32352342
2222     897897
2222     32352342
2222     222222222
3333     222222222

and the table filetypeversion:

FileTypeVerID      PubID       FileTypeID      IsRelatedTo    IsPreviousVersionOf
897897             789789798   6575            dw             qweq
32352342           68767       231232          dasdasda       asdasda
222222222          333333      231231          asd            wdadw

and the table softwareversion:

SWVerID  SWID    Name
1111     1234    Adobe Reader
2222     5678    Word
3333     4444    ExcelVersion

I am using an SQL command to retrieve the SWVerID values from the softwareversion table associated with the FileTypeVerID 897897 value AND WHICH ARE NOT associated with the 222222222 value from the swverforftver table.

The command I am using is the following:

SELECT  distinct softwareversion.SWVerID 
FROM    softwareversion,
        filetypeversion,
        swverforftver AS stv JOIN 
        swverforftver AS stv2 ON stv2.SWVerID = stv.SWVerID 
WHERE   stv2.FileTypeVerID=filetypeversion.FileTypeVerID 
AND     stv.SWVerID=softwareversion.SWVerID 
AND     (stv2.FileTypeVerID = 897897) 
AND NOT (stv.FileTypeVerID = 222222222)

Instead of returning me only the 11开发者_开发技巧11 value, being the only value associated with 897897 and not with 222222222, it returns me a tables with two values:

SWVerID
1111
2222

Does anyone know something about this issue? Also the SQL command should not be modified in structure from the current one (it should have the JOIN and AS basically)...

You get the second row in your result set because there exist 3 rows for SWVerID = 2222, so the condition is evaluated to true for this. Use a NOT IN instead:

SELECT  distinct softwareversion.SWVerID 
FROM    softwareversion,
        filetypeversion,
        swverforftver AS stv 
WHERE   stv2.FileTypeVerID=filetypeversion.FileTypeVerID 
AND     stv.SWVerID=softwareversion.SWVerID 
AND     (stv2.FileTypeVerID = 897897) 
AND NOT EXISTS (select null from swverforftver AS stv2 WHERE stv2.SWVerID = stv.SWVerID AND 
stv2.FileTypeVerID = 222222222)

Use the not equal operator instead:

SELECT  distinct softwareversion.SWVerID 
FROM    softwareversion,filetypeversion,swverforftver AS stv JOIN 
        swverforftver AS stv2 ON stv2.SWVerID = stv.SWVerID 
WHERE   stv2.FileTypeVerID=filetypeversion.FileTypeVerID 
AND     stv.SWVerID=softwareversion.SWVerID 
AND     (stv2.FileTypeVerID = 897897) 
AND     (stv.FileTypeVerID != 222222222)

Not sure I'm making full sense of your question, but your not() clause is likely eliminating the wrong rows.

To debug your sql statement, list all of the columns:

select *
from ...
join ...
where ...

This will reveal rows as they're seen by the database before they get filtered. It'll likely highlight that you're trying to eliminate SWVerID, rather than FileTypeVerID, and that you should be using an anti-join instead: SWVerID not in (subquery)

Your query will still return 2222 because there are records which satisfy the condition NOT (stv.FileTypeVerID = 222222222). I would use a NOT IN to eliminate any SWVerID which has a FileTypeVerID = 222222222

SELECT  distinct softwareversion.SWVerID  
FROM softwareversion,filetypeversion,swverforftver AS stv 
WHERE stv2.FileTypeVerID=filetypeversion.FileTypeVerID  
AND stv.SWVerID=softwareversion.SWVerID 
AND stv.FileTypeVerID = 897897
AND stv.SWVerID NOT IN (
    SELECT DISTINCT SWVerID FROM swverforftver
    WHERE FileTypeVerID = 222222222
)