开发者

grouping IN statement sql part 2( negating it)

I will like to do the opposite of THIS. In other words how could I do something like:

SELECT * 
  FROM table1 
 WHERE col1, col2 NOT IN (SELECT col1, col2 
                            FROM table1
                           WHERE col3 < 4)

Not开发者_StackOverflowe the NOT clause.

EDIT

I am editing the question to explain what I am trying to do.

so I have the following table:

grouping IN statement sql part 2( negating it)

note that every time I scan a directory I enter the files that where found in this table (table1). By looking at the table we know that the first time I scan the directory there where files A and B. The second time we scan the directory we can say that files A and B where still there with an additional file C. we also know that on the second scan file A was modified because it has a different dateModified. I am interested in the last scan. From looking at it I know that we still have files A, B, and C with the addition of file X and modification of file B. That's why I wanted to build the query

select * from table1 
WHERE FileID, DateModified NOT IN 
(SELECT Path, DateModified From table1 WHERE DateInserted<4) 

hoping to get files X and B because those are the files that are not in all records with DateInserted<4 . Sorry for not explaining my self clearly.


You didn't like my answer there, I will post it here and will add NOT :).

SELECT *
FROM table1 tb1
WHERE NOT EXISTS
(
    SELECT *
    FROM table1 tb2
    WHERE 
        tb1.col1 = tb2.col1 and 
        tb1.col2 = tb2.col2 and
        tb2.col3 < 4
)

And like I said there, the query as is does not make sense as it is equivalent of

SELECT *
FROM table1 tb1
WHERE tb2.col3 >= 4

[EDIT] Update is based on the comment:

SELECT *
FROM tableSource tb1
WHERE NOT EXISTS
(
    SELECT *
    FROM tableDestination tb2
    WHERE 
        tb1.col1 = tb2.col1 and 
        tb1.col2 = tb2.col2 and
        tb2.col3 < 4
)


SELECT * from table1 t1, table1 t2
where t1.col3=t2.col3 and t1.COl4=t2.Col4 AND 
t1.col1!=t2.col1 and t1.COl2!=t2.Col2 and t1.col3<4

Try the above Query


SELECT *
FROM table1 t1
  LEFT JOIN table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t2.col3 < 4
WHERE t2.col1 IS NULL  /* col2 or col3 would do as well,
                          just like any non-nullable column would */
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜