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:
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 */
精彩评论