Geek asks and answers

Problem with AND NOT in MySql

I am starting SQL and I am working on a PHP + MySql application. The database used by the application has many tables, such as swverforftver:

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

and filetypeversion table:

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

and softwareversion table:

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

I am using the SQL command to retrieve the SWVerID values ​​from the softwareversion table associated with the FileTypeVerID value 897897 and which is NOT associated with the value 222222222 from the swverforftver table.

The command I use 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 to me only the value 1111, which is the only value associated with 897897 and not 222222222, it returns me tables with two values:

SWVerID
1111
2222

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

+3
4

, SWVerID = 2222 3 , true. NOT IN:

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)
+1

:

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

, , not() .

sql, :

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

, , . , , SWVerID, FileTypeVerID, -: SWVerID not in (subquery)

0

2222, , NOT (stv.FileTypeVerID = 222222222). NOT IN SWVerID, 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
)
0
source

More articles:


All Articles